{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WwNYSFfH3Zzk"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/AI4Finance-Foundation/FinRL-Tutorials/blob/master/1-Introduction/Stock_Fundamental.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gXaoZs2lh1hi"
   },
   "source": [
    "# Stock trading with fundamentals\n",
    "\n",
    "* This notebook is based on the tutorial: https://towardsdatascience.com/finrl-for-quantitative-finance-tutorial-for-multiple-stock-trading-7b00763b7530\n",
    "\n",
    "* This project is a result of the almuni-mentored research project at Columbia University, Application of Reinforcement Learning to Finance.\n",
    "* For detailed explanation, please check out the Medium article: https://medium.com/@mariko.sawada1/automated-stock-trading-with-deep-reinforcement-learning-and-financial-data-a63286ccbe2b\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lGunVt8oLCVS"
   },
   "source": [
    "# Content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HOzAKQ-SLGX6"
   },
   "source": [
    "* [1. Task Discription](#0)\n",
    "* [2. Install Python packages](#1)\n",
    "    * [2.1. Install Packages](#1.1)    \n",
    "    * [2.2. A List of Python Packages](#1.2)\n",
    "    * [2.3. Import Packages](#1.3)\n",
    "    * [2.4. Create Folders](#1.4)\n",
    "* [3. Download Data](#2)\n",
    "* [4. Preprocess fundamental Data](#3)        \n",
    "    * [4.1 Import financial data](#3.1)\n",
    "    * [4.2 Specify items needed to calculate financial ratios](#3.2)\n",
    "    * [4.3 Calculate financial ratios](#3.3)\n",
    "    * [4.4 Deal with NAs and infinite values](#3.4)\n",
    "    * [4.5 Merge stock price data and ratios into one dataframe](#3.5)\n",
    "    * [4.6 Calculate market valuation ratios using daily stock price data](#3.6)\n",
    "* [5. Build Environment](#4)  \n",
    "    * [5.1. Training & Trade Data Split](#4.1)\n",
    "    * [5.2. User-defined Environment](#4.2)   \n",
    "    * [5.3. Initialize Environment](#4.3)    \n",
    "* [6. Train DRL Agents](#5)  \n",
    "* [7. Backtesting Performance](#6)  \n",
    "    * [7.1. BackTestStats](#6.1)\n",
    "    * [7.2. BackTestPlot](#6.2)   \n",
    "    * [7.3. Baseline Stats](#6.3)   \n",
    "    * [7.3. Compare to Stock Market Index](#6.4)             "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "sApkDlD9LIZv"
   },
   "source": [
    "<a id='0'></a>\n",
    "# Part 1. Task Description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HjLD2TZSLKZ-"
   },
   "source": [
    "We train a DRL agent for stock trading. The task is modeled as a Markov Decision Process (MDP), and the objective function is maximizing (expected) cumulative return.\n",
    "\n",
    "We specify the state-action-reward as follows:\n",
    "\n",
    "* **State s**: The state space represents an agent's perception of the market environment. Like a human trader analyzes various information, here our agent passively observes many features and learn by interacting with the market environment (usually by replaying historical data).\n",
    "\n",
    "* **Action a**: The action space includes allowed actions that an agent can take at each state. For example, a ∈ {−1, 0, 1}, where −1, 0, 1 represent\n",
    "selling, holding, and buying. When an action operates multiple shares, a ∈{−k, ..., −1, 0, 1, ..., k}, e.g.. \"Buy\n",
    "10 shares of AAPL\" or \"Sell 10 shares of AAPL\" are 10 or −10, respectively\n",
    "\n",
    "* **Reward function r(s, a, s′)**: Reward is an incentive for an agent to learn a better policy. For example, it can be the change of the portfolio value when taking a at state s and arriving at new state s',  i.e., r(s, a, s′) = v′ − v, where v′ and v represent the portfolio values at state s′ and s, respectively\n",
    "\n",
    "\n",
    "**Market environment**: 30 consituent stocks of Dow Jones Industrial Average (DJIA) index. Accessed at the starting date of the testing period.\n",
    "\n",
    "\n",
    "The data of the single stock that we will use for this case study is obtained from Yahoo Finance API. The data contains Open-High-Low-Close prices and volume.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ffsre789LY08"
   },
   "source": [
    "<a id='1'></a>\n",
    "# Part 2. Load Python Packages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Uy5_PTmOh1hj"
   },
   "source": [
    "<a id='1.1'></a>\n",
    "## 2.1. Install all the packages through FinRL library\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "mPT0ipYE28wL",
    "outputId": "a772035e-c393-4748-c2f2-76e98ddb0937"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
      "Collecting git+https://github.com/AI4Finance-Foundation/FinRL.git\n",
      "  Cloning https://github.com/AI4Finance-Foundation/FinRL.git to /tmp/pip-req-build-12pldxg9\n",
      "  Running command git clone -q https://github.com/AI4Finance-Foundation/FinRL.git /tmp/pip-req-build-12pldxg9\n",
      "Collecting pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2\n",
      "  Cloning https://github.com/quantopian/pyfolio.git to /tmp/pip-install-q1lnps0s/pyfolio_6af1df2b000040679317b9128ed98859\n",
      "  Running command git clone -q https://github.com/quantopian/pyfolio.git /tmp/pip-install-q1lnps0s/pyfolio_6af1df2b000040679317b9128ed98859\n",
      "Collecting elegantrl@ git+https://github.com/AI4Finance-Foundation/ElegantRL.git#egg=elegantrl\n",
      "  Cloning https://github.com/AI4Finance-Foundation/ElegantRL.git to /tmp/pip-install-q1lnps0s/elegantrl_06856331265340b2819466acb98f5328\n",
      "  Running command git clone -q https://github.com/AI4Finance-Foundation/ElegantRL.git /tmp/pip-install-q1lnps0s/elegantrl_06856331265340b2819466acb98f5328\n",
      "Collecting alpaca_trade_api>=2.1.0\n",
      "  Downloading alpaca_trade_api-2.3.0-py3-none-any.whl (33 kB)\n",
      "Collecting ccxt==1.66.32\n",
      "  Downloading ccxt-1.66.32-py2.py3-none-any.whl (2.3 MB)\n",
      "\u001B[K     |████████████████████████████████| 2.3 MB 6.3 MB/s \n",
      "\u001B[?25hCollecting elegantrl\n",
      "  Downloading elegantrl-0.3.3-py3-none-any.whl (234 kB)\n",
      "\u001B[K     |████████████████████████████████| 234 kB 62.9 MB/s \n",
      "\u001B[?25hCollecting pandas_market_calendars==3.6.3\n",
      "  Downloading pandas_market_calendars-3.6.3.tar.gz (152 kB)\n",
      "\u001B[K     |████████████████████████████████| 152 kB 67.9 MB/s \n",
      "\u001B[?25hCollecting gputil\n",
      "  Downloading GPUtil-1.4.0.tar.gz (5.5 kB)\n",
      "Requirement already satisfied: gym>=0.17 in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (0.25.2)\n",
      "Collecting jqdatasdk\n",
      "  Downloading jqdatasdk-1.8.11-py3-none-any.whl (158 kB)\n",
      "\u001B[K     |████████████████████████████████| 158 kB 66.5 MB/s \n",
      "\u001B[?25hCollecting lz4\n",
      "  Downloading lz4-4.0.2-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.2 MB)\n",
      "\u001B[K     |████████████████████████████████| 1.2 MB 53.9 MB/s \n",
      "\u001B[?25hRequirement already satisfied: matplotlib in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (3.2.2)\n",
      "Requirement already satisfied: numpy>=1.17.3 in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (1.21.6)\n",
      "Requirement already satisfied: pandas>=1.1.5 in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (1.3.5)\n",
      "Collecting pre-commit\n",
      "  Downloading pre_commit-2.20.0-py2.py3-none-any.whl (199 kB)\n",
      "\u001B[K     |████████████████████████████████| 199 kB 70.1 MB/s \n",
      "\u001B[?25hRequirement already satisfied: pytest in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (3.6.4)\n",
      "Collecting ray[default]\n",
      "  Downloading ray-2.0.0-cp37-cp37m-manylinux2014_x86_64.whl (59.4 MB)\n",
      "\u001B[K     |████████████████████████████████| 59.4 MB 1.2 MB/s \n",
      "\u001B[?25hRequirement already satisfied: scikit-learn>=0.21.0 in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (1.0.2)\n",
      "Collecting setuptools==59.5.0\n",
      "  Downloading setuptools-59.5.0-py3-none-any.whl (952 kB)\n",
      "\u001B[K     |████████████████████████████████| 952 kB 43.9 MB/s \n",
      "\u001B[?25hCollecting stable-baselines3[extra]\n",
      "  Downloading stable_baselines3-1.6.0-py3-none-any.whl (177 kB)\n",
      "\u001B[K     |████████████████████████████████| 177 kB 49.9 MB/s \n",
      "\u001B[?25hCollecting stockstats>=0.4.0\n",
      "  Downloading stockstats-0.4.1-py2.py3-none-any.whl (19 kB)\n",
      "Collecting tensorboardX\n",
      "  Downloading tensorboardX-2.5.1-py2.py3-none-any.whl (125 kB)\n",
      "\u001B[K     |████████████████████████████████| 125 kB 51.2 MB/s \n",
      "\u001B[?25hRequirement already satisfied: wheel>=0.33.6 in /usr/local/lib/python3.7/dist-packages (from finrl==0.3.5) (0.37.1)\n",
      "Collecting wrds\n",
      "  Downloading wrds-3.1.2-py3-none-any.whl (13 kB)\n",
      "Collecting yfinance\n",
      "  Downloading yfinance-0.1.74-py2.py3-none-any.whl (27 kB)\n",
      "Collecting pybullet\n",
      "  Downloading pybullet-3.2.5-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.whl (91.7 MB)\n",
      "\u001B[K     |████████████████████████████████| 91.7 MB 33 kB/s \n",
      "\u001B[?25hRequirement already satisfied: torch in /usr/local/lib/python3.7/dist-packages (from elegantrl@ git+https://github.com/AI4Finance-Foundation/ElegantRL.git#egg=elegantrl->finrl==0.3.5) (1.12.1+cu113)\n",
      "Requirement already satisfied: opencv-python in /usr/local/lib/python3.7/dist-packages (from elegantrl@ git+https://github.com/AI4Finance-Foundation/ElegantRL.git#egg=elegantrl->finrl==0.3.5) (4.6.0.66)\n",
      "Collecting box2d-py\n",
      "  Downloading box2d_py-2.3.8-cp37-cp37m-manylinux1_x86_64.whl (448 kB)\n",
      "\u001B[K     |████████████████████████████████| 448 kB 28.7 MB/s \n",
      "\u001B[?25hRequirement already satisfied: ipython>=3.2.3 in /usr/local/lib/python3.7/dist-packages (from pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (7.9.0)\n",
      "Requirement already satisfied: pytz>=2014.10 in /usr/local/lib/python3.7/dist-packages (from pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (2022.2.1)\n",
      "Requirement already satisfied: scipy>=0.14.0 in /usr/local/lib/python3.7/dist-packages (from pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (1.7.3)\n",
      "Requirement already satisfied: seaborn>=0.7.1 in /usr/local/lib/python3.7/dist-packages (from pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.11.2)\n",
      "Collecting empyrical>=0.5.0\n",
      "  Downloading empyrical-0.5.5.tar.gz (52 kB)\n",
      "\u001B[K     |████████████████████████████████| 52 kB 1.1 MB/s \n",
      "\u001B[?25hCollecting aiodns>=1.1.1\n",
      "  Downloading aiodns-3.0.0-py3-none-any.whl (5.0 kB)\n",
      "Requirement already satisfied: certifi>=2018.1.18 in /usr/local/lib/python3.7/dist-packages (from ccxt==1.66.32->finrl==0.3.5) (2022.6.15)\n",
      "Collecting yarl==1.7.2\n",
      "  Downloading yarl-1.7.2-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (271 kB)\n",
      "\u001B[K     |████████████████████████████████| 271 kB 43.2 MB/s \n",
      "\u001B[?25hRequirement already satisfied: aiohttp>=3.8 in /usr/local/lib/python3.7/dist-packages (from ccxt==1.66.32->finrl==0.3.5) (3.8.1)\n",
      "Requirement already satisfied: requests>=2.18.4 in /usr/local/lib/python3.7/dist-packages (from ccxt==1.66.32->finrl==0.3.5) (2.23.0)\n",
      "Collecting cryptography>=2.6.1\n",
      "  Downloading cryptography-38.0.1-cp36-abi3-manylinux_2_24_x86_64.whl (4.0 MB)\n",
      "\u001B[K     |████████████████████████████████| 4.0 MB 32.5 MB/s \n",
      "\u001B[?25hCollecting pyluach\n",
      "  Downloading pyluach-2.0.1-py3-none-any.whl (22 kB)\n",
      "Requirement already satisfied: python-dateutil in /usr/local/lib/python3.7/dist-packages (from pandas_market_calendars==3.6.3->finrl==0.3.5) (2.8.2)\n",
      "Requirement already satisfied: toolz in /usr/local/lib/python3.7/dist-packages (from pandas_market_calendars==3.6.3->finrl==0.3.5) (0.12.0)\n",
      "Requirement already satisfied: korean_lunar_calendar in /usr/local/lib/python3.7/dist-packages (from pandas_market_calendars==3.6.3->finrl==0.3.5) (0.2.1)\n",
      "Requirement already satisfied: multidict>=4.0 in /usr/local/lib/python3.7/dist-packages (from yarl==1.7.2->ccxt==1.66.32->finrl==0.3.5) (6.0.2)\n",
      "Requirement already satisfied: typing-extensions>=3.7.4 in /usr/local/lib/python3.7/dist-packages (from yarl==1.7.2->ccxt==1.66.32->finrl==0.3.5) (4.1.1)\n",
      "Requirement already satisfied: idna>=2.0 in /usr/local/lib/python3.7/dist-packages (from yarl==1.7.2->ccxt==1.66.32->finrl==0.3.5) (2.10)\n",
      "Collecting pycares>=4.0.0\n",
      "  Downloading pycares-4.2.2-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (288 kB)\n",
      "\u001B[K     |████████████████████████████████| 288 kB 23.2 MB/s \n",
      "\u001B[?25hRequirement already satisfied: async-timeout<5.0,>=4.0.0a3 in /usr/local/lib/python3.7/dist-packages (from aiohttp>=3.8->ccxt==1.66.32->finrl==0.3.5) (4.0.2)\n",
      "Requirement already satisfied: charset-normalizer<3.0,>=2.0 in /usr/local/lib/python3.7/dist-packages (from aiohttp>=3.8->ccxt==1.66.32->finrl==0.3.5) (2.1.1)\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in /usr/local/lib/python3.7/dist-packages (from aiohttp>=3.8->ccxt==1.66.32->finrl==0.3.5) (1.3.1)\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in /usr/local/lib/python3.7/dist-packages (from aiohttp>=3.8->ccxt==1.66.32->finrl==0.3.5) (1.2.0)\n",
      "Requirement already satisfied: attrs>=17.3.0 in /usr/local/lib/python3.7/dist-packages (from aiohttp>=3.8->ccxt==1.66.32->finrl==0.3.5) (22.1.0)\n",
      "Requirement already satisfied: asynctest==0.13.0 in /usr/local/lib/python3.7/dist-packages (from aiohttp>=3.8->ccxt==1.66.32->finrl==0.3.5) (0.13.0)\n",
      "Collecting websocket-client<2,>=0.56.0\n",
      "  Downloading websocket_client-1.4.1-py3-none-any.whl (55 kB)\n",
      "\u001B[K     |████████████████████████████████| 55 kB 2.6 MB/s \n",
      "\u001B[?25hRequirement already satisfied: urllib3<2,>1.24 in /usr/local/lib/python3.7/dist-packages (from alpaca_trade_api>=2.1.0->finrl==0.3.5) (1.24.3)\n",
      "Collecting msgpack==1.0.3\n",
      "  Downloading msgpack-1.0.3-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (299 kB)\n",
      "\u001B[K     |████████████████████████████████| 299 kB 50.3 MB/s \n",
      "\u001B[?25hCollecting websockets<11,>=9.0\n",
      "  Downloading websockets-10.3-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (112 kB)\n",
      "\u001B[K     |████████████████████████████████| 112 kB 28.7 MB/s \n",
      "\u001B[?25hRequirement already satisfied: PyYAML==6.0 in /usr/local/lib/python3.7/dist-packages (from alpaca_trade_api>=2.1.0->finrl==0.3.5) (6.0)\n",
      "Collecting deprecation==2.1.0\n",
      "  Downloading deprecation-2.1.0-py2.py3-none-any.whl (11 kB)\n",
      "Requirement already satisfied: packaging in /usr/local/lib/python3.7/dist-packages (from deprecation==2.1.0->alpaca_trade_api>=2.1.0->finrl==0.3.5) (21.3)\n",
      "Requirement already satisfied: cffi>=1.12 in /usr/local/lib/python3.7/dist-packages (from cryptography>=2.6.1->ccxt==1.66.32->finrl==0.3.5) (1.15.1)\n",
      "Requirement already satisfied: pycparser in /usr/local/lib/python3.7/dist-packages (from cffi>=1.12->cryptography>=2.6.1->ccxt==1.66.32->finrl==0.3.5) (2.21)\n",
      "Requirement already satisfied: pandas-datareader>=0.2 in /usr/local/lib/python3.7/dist-packages (from empyrical>=0.5.0->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.9.0)\n",
      "Requirement already satisfied: importlib-metadata>=4.8.0 in /usr/local/lib/python3.7/dist-packages (from gym>=0.17->finrl==0.3.5) (4.12.0)\n",
      "Requirement already satisfied: cloudpickle>=1.2.0 in /usr/local/lib/python3.7/dist-packages (from gym>=0.17->finrl==0.3.5) (1.5.0)\n",
      "Requirement already satisfied: gym-notices>=0.0.4 in /usr/local/lib/python3.7/dist-packages (from gym>=0.17->finrl==0.3.5) (0.0.8)\n",
      "Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=4.8.0->gym>=0.17->finrl==0.3.5) (3.8.1)\n",
      "Requirement already satisfied: pickleshare in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.7.5)\n",
      "Requirement already satisfied: prompt-toolkit<2.1.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (2.0.10)\n",
      "Requirement already satisfied: backcall in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.2.0)\n",
      "Requirement already satisfied: pexpect in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (4.8.0)\n",
      "Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (5.1.1)\n",
      "Requirement already satisfied: decorator in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (4.4.2)\n",
      "Requirement already satisfied: pygments in /usr/local/lib/python3.7/dist-packages (from ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (2.6.1)\n",
      "Collecting jedi>=0.10\n",
      "  Downloading jedi-0.18.1-py2.py3-none-any.whl (1.6 MB)\n",
      "\u001B[K     |████████████████████████████████| 1.6 MB 30.4 MB/s \n",
      "\u001B[?25hRequirement already satisfied: parso<0.9.0,>=0.8.0 in /usr/local/lib/python3.7/dist-packages (from jedi>=0.10->ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.8.3)\n",
      "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib->finrl==0.3.5) (3.0.9)\n",
      "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.7/dist-packages (from matplotlib->finrl==0.3.5) (0.11.0)\n",
      "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib->finrl==0.3.5) (1.4.4)\n",
      "Requirement already satisfied: lxml in /usr/local/lib/python3.7/dist-packages (from pandas-datareader>=0.2->empyrical>=0.5.0->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (4.9.1)\n",
      "Requirement already satisfied: six>=1.9.0 in /usr/local/lib/python3.7/dist-packages (from prompt-toolkit<2.1.0,>=2.0.0->ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (1.15.0)\n",
      "Requirement already satisfied: wcwidth in /usr/local/lib/python3.7/dist-packages (from prompt-toolkit<2.1.0,>=2.0.0->ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.2.5)\n",
      "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests>=2.18.4->ccxt==1.66.32->finrl==0.3.5) (3.0.4)\n",
      "Requirement already satisfied: joblib>=0.11 in /usr/local/lib/python3.7/dist-packages (from scikit-learn>=0.21.0->finrl==0.3.5) (1.1.0)\n",
      "Requirement already satisfied: threadpoolctl>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from scikit-learn>=0.21.0->finrl==0.3.5) (3.1.0)\n",
      "Collecting pymysql>=0.7.6\n",
      "  Downloading PyMySQL-1.0.2-py3-none-any.whl (43 kB)\n",
      "\u001B[K     |████████████████████████████████| 43 kB 1.5 MB/s \n",
      "\u001B[?25hRequirement already satisfied: SQLAlchemy>=1.2.8 in /usr/local/lib/python3.7/dist-packages (from jqdatasdk->finrl==0.3.5) (1.4.41)\n",
      "Collecting thriftpy2>=0.3.9\n",
      "  Downloading thriftpy2-0.4.14.tar.gz (361 kB)\n",
      "\u001B[K     |████████████████████████████████| 361 kB 39.7 MB/s \n",
      "\u001B[?25hRequirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.7/dist-packages (from SQLAlchemy>=1.2.8->jqdatasdk->finrl==0.3.5) (1.1.3)\n",
      "Collecting ply<4.0,>=3.4\n",
      "  Downloading ply-3.11-py2.py3-none-any.whl (49 kB)\n",
      "\u001B[K     |████████████████████████████████| 49 kB 4.6 MB/s \n",
      "\u001B[?25hRequirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.7/dist-packages (from pexpect->ipython>=3.2.3->pyfolio@ git+https://github.com/quantopian/pyfolio.git#egg=pyfolio-0.9.2->finrl==0.3.5) (0.7.0)\n",
      "Collecting identify>=1.0.0\n",
      "  Downloading identify-2.5.5-py2.py3-none-any.whl (98 kB)\n",
      "\u001B[K     |████████████████████████████████| 98 kB 7.7 MB/s \n",
      "\u001B[?25hCollecting cfgv>=2.0.0\n",
      "  Downloading cfgv-3.3.1-py2.py3-none-any.whl (7.3 kB)\n",
      "Collecting nodeenv>=0.11.1\n",
      "  Downloading nodeenv-1.7.0-py2.py3-none-any.whl (21 kB)\n",
      "Requirement already satisfied: toml in /usr/local/lib/python3.7/dist-packages (from pre-commit->finrl==0.3.5) (0.10.2)\n",
      "Collecting virtualenv>=20.0.8\n",
      "  Downloading virtualenv-20.16.5-py3-none-any.whl (8.8 MB)\n",
      "\u001B[K     |████████████████████████████████| 8.8 MB 38.5 MB/s \n",
      "\u001B[?25hCollecting distlib<1,>=0.3.5\n",
      "  Downloading distlib-0.3.6-py2.py3-none-any.whl (468 kB)\n",
      "\u001B[K     |████████████████████████████████| 468 kB 50.7 MB/s \n",
      "\u001B[?25hRequirement already satisfied: filelock<4,>=3.4.1 in /usr/local/lib/python3.7/dist-packages (from virtualenv>=20.0.8->pre-commit->finrl==0.3.5) (3.8.0)\n",
      "Collecting platformdirs<3,>=2.4\n",
      "  Downloading platformdirs-2.5.2-py3-none-any.whl (14 kB)\n",
      "Requirement already satisfied: atomicwrites>=1.0 in /usr/local/lib/python3.7/dist-packages (from pytest->finrl==0.3.5) (1.4.1)\n",
      "Requirement already satisfied: py>=1.5.0 in /usr/local/lib/python3.7/dist-packages (from pytest->finrl==0.3.5) (1.11.0)\n",
      "Requirement already satisfied: pluggy<0.8,>=0.5 in /usr/local/lib/python3.7/dist-packages (from pytest->finrl==0.3.5) (0.7.1)\n",
      "Requirement already satisfied: more-itertools>=4.0.0 in /usr/local/lib/python3.7/dist-packages (from pytest->finrl==0.3.5) (8.14.0)\n",
      "Requirement already satisfied: click<=8.0.4,>=7.0 in /usr/local/lib/python3.7/dist-packages (from ray[default]->finrl==0.3.5) (7.1.2)\n",
      "Collecting grpcio<=1.43.0,>=1.28.1\n",
      "  Downloading grpcio-1.43.0-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (4.1 MB)\n",
      "\u001B[K     |████████████████████████████████| 4.1 MB 37.3 MB/s \n",
      "\u001B[?25hRequirement already satisfied: protobuf<4.0.0,>=3.15.3 in /usr/local/lib/python3.7/dist-packages (from ray[default]->finrl==0.3.5) (3.17.3)\n",
      "Requirement already satisfied: jsonschema in /usr/local/lib/python3.7/dist-packages (from ray[default]->finrl==0.3.5) (4.3.3)\n",
      "Collecting gpustat>=1.0.0b1\n",
      "  Downloading gpustat-1.0.0.tar.gz (90 kB)\n",
      "\u001B[K     |████████████████████████████████| 90 kB 7.3 MB/s \n",
      "\u001B[?25hCollecting py-spy>=0.2.0\n",
      "  Downloading py_spy-0.3.14-py2.py3-none-manylinux_2_5_x86_64.manylinux1_x86_64.whl (3.0 MB)\n",
      "\u001B[K     |████████████████████████████████| 3.0 MB 38.2 MB/s \n",
      "\u001B[?25hCollecting opencensus\n",
      "  Downloading opencensus-0.11.0-py2.py3-none-any.whl (128 kB)\n",
      "\u001B[K     |████████████████████████████████| 128 kB 57.5 MB/s \n",
      "\u001B[?25hCollecting aiohttp-cors\n",
      "  Downloading aiohttp_cors-0.7.0-py3-none-any.whl (27 kB)\n",
      "Collecting colorful\n",
      "  Downloading colorful-0.5.4-py2.py3-none-any.whl (201 kB)\n",
      "\u001B[K     |████████████████████████████████| 201 kB 47.9 MB/s \n",
      "\u001B[?25hCollecting prometheus-client<0.14.0,>=0.7.1\n",
      "  Downloading prometheus_client-0.13.1-py3-none-any.whl (57 kB)\n",
      "\u001B[K     |████████████████████████████████| 57 kB 4.7 MB/s \n",
      "\u001B[?25hRequirement already satisfied: pydantic in /usr/local/lib/python3.7/dist-packages (from ray[default]->finrl==0.3.5) (1.9.2)\n",
      "Requirement already satisfied: smart-open in /usr/local/lib/python3.7/dist-packages (from ray[default]->finrl==0.3.5) (5.2.1)\n",
      "Collecting nvidia-ml-py<=11.495.46,>=11.450.129\n",
      "  Downloading nvidia_ml_py-11.495.46-py3-none-any.whl (25 kB)\n",
      "Collecting psutil>=5.6.0\n",
      "  Downloading psutil-5.9.2-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (281 kB)\n",
      "\u001B[K     |████████████████████████████████| 281 kB 47.3 MB/s \n",
      "\u001B[?25hCollecting blessed>=1.17.1\n",
      "  Downloading blessed-1.19.1-py2.py3-none-any.whl (58 kB)\n",
      "\u001B[K     |████████████████████████████████| 58 kB 5.5 MB/s \n",
      "\u001B[?25hRequirement already satisfied: pyrsistent!=0.17.0,!=0.17.1,!=0.17.2,>=0.14.0 in /usr/local/lib/python3.7/dist-packages (from jsonschema->ray[default]->finrl==0.3.5) (0.18.1)\n",
      "Requirement already satisfied: importlib-resources>=1.4.0 in /usr/local/lib/python3.7/dist-packages (from jsonschema->ray[default]->finrl==0.3.5) (5.9.0)\n",
      "Collecting opencensus-context>=0.1.3\n",
      "  Downloading opencensus_context-0.1.3-py2.py3-none-any.whl (5.1 kB)\n",
      "Requirement already satisfied: google-api-core<3.0.0,>=1.0.0 in /usr/local/lib/python3.7/dist-packages (from opencensus->ray[default]->finrl==0.3.5) (1.31.6)\n",
      "Requirement already satisfied: google-auth<2.0dev,>=1.25.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3.0.0,>=1.0.0->opencensus->ray[default]->finrl==0.3.5) (1.35.0)\n",
      "Requirement already satisfied: googleapis-common-protos<2.0dev,>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from google-api-core<3.0.0,>=1.0.0->opencensus->ray[default]->finrl==0.3.5) (1.56.4)\n",
      "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.25.0->google-api-core<3.0.0,>=1.0.0->opencensus->ray[default]->finrl==0.3.5) (4.9)\n",
      "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.25.0->google-api-core<3.0.0,>=1.0.0->opencensus->ray[default]->finrl==0.3.5) (0.2.8)\n",
      "Requirement already satisfied: cachetools<5.0,>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from google-auth<2.0dev,>=1.25.0->google-api-core<3.0.0,>=1.0.0->opencensus->ray[default]->finrl==0.3.5) (4.2.4)\n",
      "Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /usr/local/lib/python3.7/dist-packages (from pyasn1-modules>=0.2.1->google-auth<2.0dev,>=1.25.0->google-api-core<3.0.0,>=1.0.0->opencensus->ray[default]->finrl==0.3.5) (0.4.8)\n",
      "Requirement already satisfied: tabulate in /usr/local/lib/python3.7/dist-packages (from ray[default]->finrl==0.3.5) (0.8.10)\n",
      "Collecting gym>=0.17\n",
      "  Downloading gym-0.21.0.tar.gz (1.5 MB)\n",
      "\u001B[K     |████████████████████████████████| 1.5 MB 57.7 MB/s \n",
      "\u001B[?25hCollecting ale-py==0.7.4\n",
      "  Downloading ale_py-0.7.4-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.6 MB)\n",
      "\u001B[K     |████████████████████████████████| 1.6 MB 41.1 MB/s \n",
      "\u001B[?25hCollecting autorom[accept-rom-license]~=0.4.2\n",
      "  Downloading AutoROM-0.4.2-py3-none-any.whl (16 kB)\n",
      "Requirement already satisfied: pillow in /usr/local/lib/python3.7/dist-packages (from stable-baselines3[extra]->finrl==0.3.5) (7.1.2)\n",
      "Requirement already satisfied: tensorboard>=2.2.0 in /usr/local/lib/python3.7/dist-packages (from stable-baselines3[extra]->finrl==0.3.5) (2.8.0)\n",
      "Collecting protobuf<4.0.0,>=3.15.3\n",
      "  Downloading protobuf-3.19.5-py2.py3-none-any.whl (162 kB)\n",
      "\u001B[K     |████████████████████████████████| 162 kB 52.2 MB/s \n",
      "\u001B[?25hRequirement already satisfied: tqdm in /usr/local/lib/python3.7/dist-packages (from autorom[accept-rom-license]~=0.4.2->stable-baselines3[extra]->finrl==0.3.5) (4.64.1)\n",
      "Collecting AutoROM.accept-rom-license\n",
      "  Downloading AutoROM.accept-rom-license-0.4.2.tar.gz (9.8 kB)\n",
      "  Installing build dependencies ... \u001B[?25l\u001B[?25hdone\n",
      "  Getting requirements to build wheel ... \u001B[?25l\u001B[?25hdone\n",
      "    Preparing wheel metadata ... \u001B[?25l\u001B[?25hdone\n",
      "Requirement already satisfied: tensorboard-plugin-wit>=1.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (1.8.1)\n",
      "Requirement already satisfied: markdown>=2.6.8 in /usr/local/lib/python3.7/dist-packages (from tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (3.4.1)\n",
      "Requirement already satisfied: absl-py>=0.4 in /usr/local/lib/python3.7/dist-packages (from tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (1.2.0)\n",
      "Requirement already satisfied: werkzeug>=0.11.15 in /usr/local/lib/python3.7/dist-packages (from tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (1.0.1)\n",
      "Requirement already satisfied: tensorboard-data-server<0.7.0,>=0.6.0 in /usr/local/lib/python3.7/dist-packages (from tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (0.6.1)\n",
      "Requirement already satisfied: google-auth-oauthlib<0.5,>=0.4.1 in /usr/local/lib/python3.7/dist-packages (from tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (0.4.6)\n",
      "Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.7/dist-packages (from google-auth-oauthlib<0.5,>=0.4.1->tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (1.3.1)\n",
      "Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.7/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib<0.5,>=0.4.1->tensorboard>=2.2.0->stable-baselines3[extra]->finrl==0.3.5) (3.2.0)\n",
      "Collecting mock\n",
      "  Downloading mock-4.0.3-py3-none-any.whl (28 kB)\n",
      "Collecting psycopg2-binary\n",
      "  Downloading psycopg2_binary-2.9.3-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.0 MB)\n",
      "\u001B[K     |████████████████████████████████| 3.0 MB 29.3 MB/s \n",
      "\u001B[?25hRequirement already satisfied: multitasking>=0.0.7 in /usr/local/lib/python3.7/dist-packages (from yfinance->finrl==0.3.5) (0.0.11)\n",
      "Collecting requests>=2.18.4\n",
      "  Using cached requests-2.28.1-py3-none-any.whl (62 kB)\n",
      "Building wheels for collected packages: finrl, elegantrl, pyfolio, exchange-calendars, empyrical, gputil, thriftpy2, gpustat, gym, AutoROM.accept-rom-license\n",
      "  Building wheel for finrl (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for finrl: filename=finrl-0.3.5-py3-none-any.whl size=2737269 sha256=59ecc863a6b769276dcc5df05919ca4da56f8f695283af222c10268605ac2518\n",
      "  Stored in directory: /tmp/pip-ephem-wheel-cache-y9no89vf/wheels/57/42/c6/458c5fd63ad5dc7005d30864147a61e41085745eb213f4ef46\n",
      "  Building wheel for elegantrl (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for elegantrl: filename=elegantrl-0.3.3-py3-none-any.whl size=347401 sha256=1c8af4b0351eb88e2f529a2d416e15469299fce6fa16c89d187ca0cfca7d2d7a\n",
      "  Stored in directory: /tmp/pip-ephem-wheel-cache-y9no89vf/wheels/99/85/5e/86cb3a9f47adfca5e248295e93113e1b298d60883126d62c84\n",
      "  Building wheel for pyfolio (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for pyfolio: filename=pyfolio-0.9.2+75.g4b901f6-py3-none-any.whl size=75774 sha256=1af0a4c41aedc08a2e6c5157a6e3a6c3476af52d2c17741ddbab7d84de2a0f5e\n",
      "  Stored in directory: /tmp/pip-ephem-wheel-cache-y9no89vf/wheels/ef/09/e5/2c1bf37c050d22557c080deb1be986d06424627c04aeca19b9\n",
      "  Building wheel for exchange-calendars (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for exchange-calendars: filename=pandas_market_calendars-3.6.3-py3-none-any.whl size=182636 sha256=c670e615abac25fc84225d343d1fbe07d8ac9d61794ac19b73b35de1d511ffee\n",
      "  Stored in directory: /root/.cache/pip/wheels/b8/a3/19/b4611514d34ffd61d13aef10fefc2dcaf3754145121ceba647\n",
      "  Building wheel for empyrical (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for empyrical: filename=empyrical-0.5.5-py3-none-any.whl size=39780 sha256=866ce0307ddf00e542a0ec542f5c483f52e42b3ac1afcc6ca4611d4e3a425696\n",
      "  Stored in directory: /root/.cache/pip/wheels/d9/91/4b/654fcff57477efcf149eaca236da2fce991526cbab431bf312\n",
      "  Building wheel for gputil (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for gputil: filename=GPUtil-1.4.0-py3-none-any.whl size=7411 sha256=b540fe54c3672d47d9d42366c1ca04fc3f63cb03a14dea86ab69108da1def66d\n",
      "  Stored in directory: /root/.cache/pip/wheels/6e/f8/83/534c52482d6da64622ddbf72cd93c35d2ef2881b78fd08ff0c\n",
      "  Building wheel for thriftpy2 (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for thriftpy2: filename=thriftpy2-0.4.14-cp37-cp37m-linux_x86_64.whl size=953370 sha256=ba920cee5a82b6f3ad78d0caf9c0990f5a80758cd6251279f7485799084525ba\n",
      "  Stored in directory: /root/.cache/pip/wheels/2a/f5/49/9c0d851aa64b58db72883cf9393cc824d536bdf13f5c83cff4\n",
      "  Building wheel for gpustat (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for gpustat: filename=gpustat-1.0.0-py3-none-any.whl size=19889 sha256=c2c0f5a3a888f78b5c0c3fd61b177983eb1674dc79c347af09d13f1bdffa5e6b\n",
      "  Stored in directory: /root/.cache/pip/wheels/d2/31/5c/eb69af6e2285e7d6ec8d7dc26435be7c81c6ad22c45efdcca7\n",
      "  Building wheel for gym (setup.py) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for gym: filename=gym-0.21.0-py3-none-any.whl size=1616825 sha256=e563f61eb7b16ad4e10792d212f7d2fd76408b22a6fb4c53acaea7cd63efe096\n",
      "  Stored in directory: /root/.cache/pip/wheels/76/ee/9c/36bfe3e079df99acf5ae57f4e3464ff2771b34447d6d2f2148\n",
      "  Building wheel for AutoROM.accept-rom-license (PEP 517) ... \u001B[?25l\u001B[?25hdone\n",
      "  Created wheel for AutoROM.accept-rom-license: filename=AutoROM.accept_rom_license-0.4.2-py3-none-any.whl size=441027 sha256=adfb632269f06c302f9fa74eace142752be806749d83194ec074e207a8eee2fa\n",
      "  Stored in directory: /root/.cache/pip/wheels/87/67/2e/6147e7912fe37f5408b80d07527dab807c1d25f5c403a9538a\n",
      "Successfully built finrl elegantrl pyfolio exchange-calendars empyrical gputil thriftpy2 gpustat gym AutoROM.accept-rom-license\n",
      "Installing collected packages: setuptools, requests, protobuf, yarl, platformdirs, distlib, virtualenv, pycares, psutil, ply, opencensus-context, nvidia-ml-py, msgpack, jedi, gym, grpcio, blessed, AutoROM.accept-rom-license, autorom, websockets, websocket-client, thriftpy2, tensorboardX, stable-baselines3, ray, pymysql, pyluach, pybullet, py-spy, psycopg2-binary, prometheus-client, opencensus, nodeenv, mock, identify, gpustat, empyrical, deprecation, cryptography, colorful, cfgv, box2d-py, ale-py, aiohttp-cors, aiodns, yfinance, wrds, stockstats, pyfolio, pre-commit, lz4, jqdatasdk, gputil, exchange-calendars, elegantrl, ccxt, alpaca-trade-api, finrl\n",
      "  Attempting uninstall: setuptools\n",
      "    Found existing installation: setuptools 57.4.0\n",
      "    Uninstalling setuptools-57.4.0:\n",
      "      Successfully uninstalled setuptools-57.4.0\n",
      "  Attempting uninstall: requests\n",
      "    Found existing installation: requests 2.23.0\n",
      "    Uninstalling requests-2.23.0:\n",
      "      Successfully uninstalled requests-2.23.0\n",
      "  Attempting uninstall: protobuf\n",
      "    Found existing installation: protobuf 3.17.3\n",
      "    Uninstalling protobuf-3.17.3:\n",
      "      Successfully uninstalled protobuf-3.17.3\n",
      "  Attempting uninstall: yarl\n",
      "    Found existing installation: yarl 1.8.1\n",
      "    Uninstalling yarl-1.8.1:\n",
      "      Successfully uninstalled yarl-1.8.1\n",
      "  Attempting uninstall: psutil\n",
      "    Found existing installation: psutil 5.4.8\n",
      "    Uninstalling psutil-5.4.8:\n",
      "      Successfully uninstalled psutil-5.4.8\n",
      "  Attempting uninstall: msgpack\n",
      "    Found existing installation: msgpack 1.0.4\n",
      "    Uninstalling msgpack-1.0.4:\n",
      "      Successfully uninstalled msgpack-1.0.4\n",
      "  Attempting uninstall: gym\n",
      "    Found existing installation: gym 0.25.2\n",
      "    Uninstalling gym-0.25.2:\n",
      "      Successfully uninstalled gym-0.25.2\n",
      "  Attempting uninstall: grpcio\n",
      "    Found existing installation: grpcio 1.48.1\n",
      "    Uninstalling grpcio-1.48.1:\n",
      "      Successfully uninstalled grpcio-1.48.1\n",
      "Successfully installed AutoROM.accept-rom-license-0.4.2 aiodns-3.0.0 aiohttp-cors-0.7.0 ale-py-0.7.4 alpaca-trade-api-2.3.0 autorom-0.4.2 blessed-1.19.1 box2d-py-2.3.8 ccxt-1.66.32 cfgv-3.3.1 colorful-0.5.4 cryptography-38.0.1 deprecation-2.1.0 distlib-0.3.6 elegantrl-0.3.3 empyrical-0.5.5 exchange-calendars-3.6.3 finrl-0.3.5 gpustat-1.0.0 gputil-1.4.0 grpcio-1.43.0 gym-0.21.0 identify-2.5.5 jedi-0.18.1 jqdatasdk-1.8.11 lz4-4.0.2 mock-4.0.3 msgpack-1.0.3 nodeenv-1.7.0 nvidia-ml-py-11.495.46 opencensus-0.11.0 opencensus-context-0.1.3 platformdirs-2.5.2 ply-3.11 pre-commit-2.20.0 prometheus-client-0.13.1 protobuf-3.19.5 psutil-5.9.2 psycopg2-binary-2.9.3 py-spy-0.3.14 pybullet-3.2.5 pycares-4.2.2 pyfolio-0.9.2+75.g4b901f6 pyluach-2.0.1 pymysql-1.0.2 ray-2.0.0 requests-2.28.1 setuptools-59.5.0 stable-baselines3-1.6.0 stockstats-0.4.1 tensorboardX-2.5.1 thriftpy2-0.4.14 virtualenv-20.16.5 websocket-client-1.4.1 websockets-10.3 wrds-3.1.2 yarl-1.7.2 yfinance-0.1.74\n"
     ]
    },
    {
     "data": {
      "application/vnd.colab-display-data+json": {
       "pip_warning": {
        "packages": [
         "google",
         "pkg_resources",
         "psutil"
        ]
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## install finrl library\n",
    "!pip install git+https://github.com/AI4Finance-Foundation/FinRL.git"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "osBHhVysOEzi"
   },
   "source": [
    "\n",
    "<a id='1.2'></a>\n",
    "## 2.2. A List of Python Packages\n",
    "* Yahoo Finance API\n",
    "* pandas\n",
    "* numpy\n",
    "* matplotlib\n",
    "* stockstats\n",
    "* OpenAI gym\n",
    "* stable-baselines\n",
    "* pyfolio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "nGv01K8Sh1hn"
   },
   "source": [
    "<a id='1.3'></a>\n",
    "## 2.3. Import Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "lPqeTTwoh1hn",
    "outputId": "8924103c-39a9-4ac9-e5c4-3f2ae6010ad9"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/pyfolio/pos.py:27: UserWarning: Module \"zipline.assets\" not found; multipliers will not be applied to position notionals.\n",
      "  'Module \"zipline.assets\" not found; multipliers will not be applied'\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "# matplotlib.use('Agg')\n",
    "import datetime\n",
    "\n",
    "%matplotlib inline\n",
    "from finrl import config\n",
    "from finrl import config_tickers\n",
    "from finrl.meta.preprocessor.yahoodownloader import YahooDownloader\n",
    "from finrl.meta.preprocessor.preprocessors import FeatureEngineer, data_split\n",
    "from finrl.meta.env_stock_trading.env_stocktrading import StockTradingEnv\n",
    "from finrl.agents.stablebaselines3.models import DRLAgent\n",
    "from finrl.plot import backtest_stats, backtest_plot, get_daily_return, get_baseline\n",
    "from finrl.main import check_and_make_directories\n",
    "from pprint import pprint\n",
    "from stable_baselines3.common.logger import configure\n",
    "import sys\n",
    "sys.path.append(\"../FinRL\")\n",
    "\n",
    "import itertools\n",
    "\n",
    "from finrl.config import (\n",
    "    DATA_SAVE_DIR,\n",
    "    TRAINED_MODEL_DIR,\n",
    "    TENSORBOARD_LOG_DIR,\n",
    "    RESULTS_DIR,\n",
    "    INDICATORS,\n",
    "    TRAIN_START_DATE,\n",
    "    TRAIN_END_DATE,\n",
    "    TEST_START_DATE,\n",
    "    TEST_END_DATE,\n",
    "    TRADE_START_DATE,\n",
    "    TRADE_END_DATE,\n",
    ")\n",
    "\n",
    "from finrl.config_tickers import DOW_30_TICKER"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "T2owTj985RW4"
   },
   "source": [
    "<a id='1.4'></a>\n",
    "## 2.4. Create Folders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "w9A8CN5R5PuZ"
   },
   "outputs": [],
   "source": [
    "check_and_make_directories([DATA_SAVE_DIR, TRAINED_MODEL_DIR, TENSORBOARD_LOG_DIR, RESULTS_DIR])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "A289rQWMh1hq"
   },
   "source": [
    "<a id='2'></a>\n",
    "# Part 3. Download Stock Data from Yahoo Finance\n",
    "Yahoo Finance provides stock data, financial news, financial reports, etc. Yahoo Finance is free.\n",
    "* FinRL uses a class **YahooDownloader** in FinRL-Meta to fetch data via Yahoo Finance API\n",
    "* Call Limit: Using the Public API (without authentication), you are limited to 2,000 requests per hour per IP (or up to a total of 48,000 requests a day).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NPeQ7iS-LoMm"
   },
   "source": [
    "\n",
    "\n",
    "-----\n",
    "class YahooDownloader:\n",
    "    Retrieving daily stock data from Yahoo Finance API\n",
    "\n",
    "    Attributes\n",
    "    ----------\n",
    "        start_date : str\n",
    "            start date of the data (modified from config.py)\n",
    "        end_date : str\n",
    "            end date of the data (modified from config.py)\n",
    "        ticker_list : list\n",
    "            a list of stock tickers (modified from config.py)\n",
    "\n",
    "    Methods\n",
    "    -------\n",
    "    fetch_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "JzqRRTOX6aFu",
    "outputId": "88b9b318-5449-4b81-cda0-133158e6b366"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AXP', 'AMGN', 'AAPL', 'BA', 'CAT', 'CSCO', 'CVX', 'GS', 'HD', 'HON', 'IBM', 'INTC', 'JNJ', 'KO', 'JPM', 'MCD', 'MMM', 'MRK', 'MSFT', 'NKE', 'PG', 'TRV', 'UNH', 'CRM', 'VZ', 'V', 'WBA', 'WMT', 'DIS', 'DOW']\n"
     ]
    }
   ],
   "source": [
    "print(DOW_30_TICKER)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "yCKm4om-s9kE",
    "outputId": "cddcf74e-50e9-4ab5-a097-1770b7633821"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "Shape of DataFrame:  (88061, 8)\n"
     ]
    }
   ],
   "source": [
    "TRAIN_START_DATE = '2009-01-01'\n",
    "TRAIN_END_DATE = '2019-01-01'\n",
    "TEST_START_DATE = '2019-01-01'\n",
    "TEST_END_DATE = '2021-01-01'\n",
    "\n",
    "df = YahooDownloader(start_date = TRAIN_START_DATE,\n",
    "                     end_date = TEST_END_DATE,\n",
    "                     ticker_list = DOW_30_TICKER).fetch_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CV3HrZHLh1hy",
    "outputId": "1e4bc45a-78a9-4e4e-8369-d7e55e58322a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(88061, 8)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "aBKF7sfV-Pi4",
    "outputId": "eabc3939-3225-46f9-fccc-25affa6ae7ee"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-9073587a-cf17-486d-9da3-e080990d80d6\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>tic</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>3.067143</td>\n",
       "      <td>3.251429</td>\n",
       "      <td>3.041429</td>\n",
       "      <td>2.767330</td>\n",
       "      <td>746015200</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>58.590000</td>\n",
       "      <td>59.080002</td>\n",
       "      <td>57.750000</td>\n",
       "      <td>44.523743</td>\n",
       "      <td>6547900</td>\n",
       "      <td>AMGN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>18.570000</td>\n",
       "      <td>19.520000</td>\n",
       "      <td>18.400000</td>\n",
       "      <td>15.477424</td>\n",
       "      <td>10955700</td>\n",
       "      <td>AXP</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>42.799999</td>\n",
       "      <td>45.560001</td>\n",
       "      <td>42.779999</td>\n",
       "      <td>33.941097</td>\n",
       "      <td>7010200</td>\n",
       "      <td>BA</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>44.910000</td>\n",
       "      <td>46.980000</td>\n",
       "      <td>44.709999</td>\n",
       "      <td>31.942245</td>\n",
       "      <td>7117200</td>\n",
       "      <td>CAT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9073587a-cf17-486d-9da3-e080990d80d6')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-9073587a-cf17-486d-9da3-e080990d80d6 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-9073587a-cf17-486d-9da3-e080990d80d6');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "         date       open       high        low      close     volume   tic  \\\n",
       "0  2009-01-02   3.067143   3.251429   3.041429   2.767330  746015200  AAPL   \n",
       "1  2009-01-02  58.590000  59.080002  57.750000  44.523743    6547900  AMGN   \n",
       "2  2009-01-02  18.570000  19.520000  18.400000  15.477424   10955700   AXP   \n",
       "3  2009-01-02  42.799999  45.560001  42.779999  33.941097    7010200    BA   \n",
       "4  2009-01-02  44.910000  46.980000  44.709999  31.942245    7117200   CAT   \n",
       "\n",
       "   day  \n",
       "0    4  \n",
       "1    4  \n",
       "2    4  \n",
       "3    4  \n",
       "4    4  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "id": "QRWscKiPXXnj"
   },
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df['date'],format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "4hYkeaPiICHS",
    "outputId": "593853c3-94cb-415b-ad96-855fd9d6603d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-e8d81960-b571-4a03-b117-124107a55258\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>tic</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>3.067143</td>\n",
       "      <td>3.251429</td>\n",
       "      <td>3.041429</td>\n",
       "      <td>2.767330</td>\n",
       "      <td>746015200</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>58.590000</td>\n",
       "      <td>59.080002</td>\n",
       "      <td>57.750000</td>\n",
       "      <td>44.523743</td>\n",
       "      <td>6547900</td>\n",
       "      <td>AMGN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>18.570000</td>\n",
       "      <td>19.520000</td>\n",
       "      <td>18.400000</td>\n",
       "      <td>15.477424</td>\n",
       "      <td>10955700</td>\n",
       "      <td>AXP</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>42.799999</td>\n",
       "      <td>45.560001</td>\n",
       "      <td>42.779999</td>\n",
       "      <td>33.941097</td>\n",
       "      <td>7010200</td>\n",
       "      <td>BA</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>44.910000</td>\n",
       "      <td>46.980000</td>\n",
       "      <td>44.709999</td>\n",
       "      <td>31.942245</td>\n",
       "      <td>7117200</td>\n",
       "      <td>CAT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e8d81960-b571-4a03-b117-124107a55258')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-e8d81960-b571-4a03-b117-124107a55258 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-e8d81960-b571-4a03-b117-124107a55258');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        date       open       high        low      close     volume   tic  day\n",
       "0 2009-01-02   3.067143   3.251429   3.041429   2.767330  746015200  AAPL    4\n",
       "1 2009-01-02  58.590000  59.080002  57.750000  44.523743    6547900  AMGN    4\n",
       "2 2009-01-02  18.570000  19.520000  18.400000  15.477424   10955700   AXP    4\n",
       "3 2009-01-02  42.799999  45.560001  42.779999  33.941097    7010200    BA    4\n",
       "4 2009-01-02  44.910000  46.980000  44.709999  31.942245    7117200   CAT    4"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['date','tic'],ignore_index=True).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uqC6c40Zh1iH"
   },
   "source": [
    "# Part 4: Preprocess fundamental data\n",
    "- Import finanical data downloaded from Compustat via WRDS(Wharton Research Data Service)\n",
    "- Preprocess the dataset and calculate financial ratios\n",
    "- Add those ratios to the price data preprocessed in Part 3\n",
    "- Calculate price-related ratios such as P/E and P/B"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VbXEllD2oROq"
   },
   "source": [
    "## 4.1 Import the financial data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "PmKP-1ii3RLS",
    "outputId": "f6b5fe8a-59eb-4e7d-eb9a-bc72c796ee5c"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:3326: DtypeWarning: Columns (16,25) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "# Import fundamental data from my GitHub repository\n",
    "url = 'https://raw.githubusercontent.com/mariko-sawada/FinRL_with_fundamental_data/main/dow_30_fundamental_wrds.csv'\n",
    "\n",
    "fund = pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 236
    },
    "id": "Tslhs_O5pOTL",
    "outputId": "2d3aeb51-ec1e-44c9-cb1c-f6a077ed11f0"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-2f3ef4db-1633-4be9-ba6e-991d6cdba6b4\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gvkey</th>\n",
       "      <th>datadate</th>\n",
       "      <th>fyearq</th>\n",
       "      <th>fqtr</th>\n",
       "      <th>fyr</th>\n",
       "      <th>indfmt</th>\n",
       "      <th>consol</th>\n",
       "      <th>popsrc</th>\n",
       "      <th>datafmt</th>\n",
       "      <th>tic</th>\n",
       "      <th>...</th>\n",
       "      <th>dvpsxq</th>\n",
       "      <th>mkvaltq</th>\n",
       "      <th>prccq</th>\n",
       "      <th>prchq</th>\n",
       "      <th>prclq</th>\n",
       "      <th>adjex</th>\n",
       "      <th>ggroup</th>\n",
       "      <th>gind</th>\n",
       "      <th>gsector</th>\n",
       "      <th>gsubind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1447</td>\n",
       "      <td>19990630</td>\n",
       "      <td>1999</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AXP</td>\n",
       "      <td>...</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130.1250</td>\n",
       "      <td>142.6250</td>\n",
       "      <td>114.5000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4020</td>\n",
       "      <td>402020</td>\n",
       "      <td>40</td>\n",
       "      <td>40202010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1447</td>\n",
       "      <td>19990930</td>\n",
       "      <td>1999</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AXP</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>135.0000</td>\n",
       "      <td>150.6250</td>\n",
       "      <td>121.8750</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4020</td>\n",
       "      <td>402020</td>\n",
       "      <td>40</td>\n",
       "      <td>40202010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1447</td>\n",
       "      <td>19991231</td>\n",
       "      <td>1999</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AXP</td>\n",
       "      <td>...</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>166.2500</td>\n",
       "      <td>168.8750</td>\n",
       "      <td>130.2500</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4020</td>\n",
       "      <td>402020</td>\n",
       "      <td>40</td>\n",
       "      <td>40202010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1447</td>\n",
       "      <td>20000331</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AXP</td>\n",
       "      <td>...</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>148.9375</td>\n",
       "      <td>169.5000</td>\n",
       "      <td>119.5000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4020</td>\n",
       "      <td>402020</td>\n",
       "      <td>40</td>\n",
       "      <td>40202010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1447</td>\n",
       "      <td>20000630</td>\n",
       "      <td>2000</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AXP</td>\n",
       "      <td>...</td>\n",
       "      <td>0.080</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.1250</td>\n",
       "      <td>57.1875</td>\n",
       "      <td>43.9375</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4020</td>\n",
       "      <td>402020</td>\n",
       "      <td>40</td>\n",
       "      <td>40202010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 647 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2f3ef4db-1633-4be9-ba6e-991d6cdba6b4')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-2f3ef4db-1633-4be9-ba6e-991d6cdba6b4 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-2f3ef4db-1633-4be9-ba6e-991d6cdba6b4');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   gvkey  datadate  fyearq  fqtr  fyr indfmt consol popsrc datafmt  tic  ...  \\\n",
       "0   1447  19990630    1999     2   12   INDL      C      D     STD  AXP  ...   \n",
       "1   1447  19990930    1999     3   12   INDL      C      D     STD  AXP  ...   \n",
       "2   1447  19991231    1999     4   12   INDL      C      D     STD  AXP  ...   \n",
       "3   1447  20000331    2000     1   12   INDL      C      D     STD  AXP  ...   \n",
       "4   1447  20000630    2000     2   12   INDL      C      D     STD  AXP  ...   \n",
       "\n",
       "  dvpsxq mkvaltq     prccq     prchq     prclq  adjex ggroup    gind gsector  \\\n",
       "0  0.225     NaN  130.1250  142.6250  114.5000    3.0   4020  402020      40   \n",
       "1  0.000     NaN  135.0000  150.6250  121.8750    3.0   4020  402020      40   \n",
       "2  0.225     NaN  166.2500  168.8750  130.2500    3.0   4020  402020      40   \n",
       "3  0.225     NaN  148.9375  169.5000  119.5000    3.0   4020  402020      40   \n",
       "4  0.080     NaN   52.1250   57.1875   43.9375    1.0   4020  402020      40   \n",
       "\n",
       "    gsubind  \n",
       "0  40202010  \n",
       "1  40202010  \n",
       "2  40202010  \n",
       "3  40202010  \n",
       "4  40202010  \n",
       "\n",
       "[5 rows x 647 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the imported dataset\n",
    "fund.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9yk1dHTYogEP"
   },
   "source": [
    "## 4.2 Specify items needed to calculate financial ratios\n",
    "- To learn more about the data description of the dataset, please check WRDS's website(https://wrds-www.wharton.upenn.edu/). Login will be required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "CD0kFC7Ap02K"
   },
   "outputs": [],
   "source": [
    "# List items that are used to calculate financial ratios\n",
    "\n",
    "items = [\n",
    "    'datadate', # Date\n",
    "    'tic', # Ticker\n",
    "    'oiadpq', # Quarterly operating income\n",
    "    'revtq', # Quartely revenue\n",
    "    'niq', # Quartely net income\n",
    "    'atq', # Total asset\n",
    "    'teqq', # Shareholder's equity\n",
    "    'epspiy', # EPS(Basic) incl. Extraordinary items\n",
    "    'ceqq', # Common Equity\n",
    "    'cshoq', # Common Shares Outstanding\n",
    "    'dvpspq', # Dividends per share\n",
    "    'actq', # Current assets\n",
    "    'lctq', # Current liabilities\n",
    "    'cheq', # Cash & Equivalent\n",
    "    'rectq', # Recievalbles\n",
    "    'cogsq', # Cost of  Goods Sold\n",
    "    'invtq', # Inventories\n",
    "    'apq',# Account payable\n",
    "    'dlttq', # Long term debt\n",
    "    'dlcq', # Debt in current liabilites\n",
    "    'ltq' # Liabilities   \n",
    "]\n",
    "\n",
    "# Omit items that will not be used\n",
    "fund_data = fund[items]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "id": "jE7UNYtIqFkv"
   },
   "outputs": [],
   "source": [
    "# Rename column names for the sake of readability\n",
    "fund_data = fund_data.rename(columns={\n",
    "    'datadate':'date', # Date\n",
    "    'oiadpq':'op_inc_q', # Quarterly operating income\n",
    "    'revtq':'rev_q', # Quartely revenue\n",
    "    'niq':'net_inc_q', # Quartely net income\n",
    "    'atq':'tot_assets', # Assets\n",
    "    'teqq':'sh_equity', # Shareholder's equity\n",
    "    'epspiy':'eps_incl_ex', # EPS(Basic) incl. Extraordinary items\n",
    "    'ceqq':'com_eq', # Common Equity\n",
    "    'cshoq':'sh_outstanding', # Common Shares Outstanding\n",
    "    'dvpspq':'div_per_sh', # Dividends per share\n",
    "    'actq':'cur_assets', # Current assets\n",
    "    'lctq':'cur_liabilities', # Current liabilities\n",
    "    'cheq':'cash_eq', # Cash & Equivalent\n",
    "    'rectq':'receivables', # Receivalbles\n",
    "    'cogsq':'cogs_q', # Cost of  Goods Sold\n",
    "    'invtq':'inventories', # Inventories\n",
    "    'apq': 'payables',# Account payable\n",
    "    'dlttq':'long_debt', # Long term debt\n",
    "    'dlcq':'short_debt', # Debt in current liabilites\n",
    "    'ltq':'tot_liabilities' # Liabilities   \n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "id": "A0sszApfqO6D",
    "outputId": "4e7a7234-dcea-40a6-dbe9-9b228e2ef29a"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-6add0f15-8cc9-473e-924f-79db07ca70e5\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>op_inc_q</th>\n",
       "      <th>rev_q</th>\n",
       "      <th>net_inc_q</th>\n",
       "      <th>tot_assets</th>\n",
       "      <th>sh_equity</th>\n",
       "      <th>eps_incl_ex</th>\n",
       "      <th>com_eq</th>\n",
       "      <th>sh_outstanding</th>\n",
       "      <th>...</th>\n",
       "      <th>cur_assets</th>\n",
       "      <th>cur_liabilities</th>\n",
       "      <th>cash_eq</th>\n",
       "      <th>receivables</th>\n",
       "      <th>cogs_q</th>\n",
       "      <th>inventories</th>\n",
       "      <th>payables</th>\n",
       "      <th>long_debt</th>\n",
       "      <th>short_debt</th>\n",
       "      <th>tot_liabilities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19990630</td>\n",
       "      <td>AXP</td>\n",
       "      <td>896.0</td>\n",
       "      <td>5564.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>132452.0</td>\n",
       "      <td>9762.0</td>\n",
       "      <td>2.73</td>\n",
       "      <td>9762.0</td>\n",
       "      <td>449.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6096.0</td>\n",
       "      <td>46774.0</td>\n",
       "      <td>4668.0</td>\n",
       "      <td>448.0</td>\n",
       "      <td>22282.0</td>\n",
       "      <td>7005.0</td>\n",
       "      <td>24785.0</td>\n",
       "      <td>122690.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19990930</td>\n",
       "      <td>AXP</td>\n",
       "      <td>906.0</td>\n",
       "      <td>5584.0</td>\n",
       "      <td>648.0</td>\n",
       "      <td>132616.0</td>\n",
       "      <td>9744.0</td>\n",
       "      <td>4.18</td>\n",
       "      <td>9744.0</td>\n",
       "      <td>447.6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5102.0</td>\n",
       "      <td>48827.0</td>\n",
       "      <td>4678.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>23587.0</td>\n",
       "      <td>6720.0</td>\n",
       "      <td>24683.0</td>\n",
       "      <td>122872.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19991231</td>\n",
       "      <td>AXP</td>\n",
       "      <td>845.0</td>\n",
       "      <td>6009.0</td>\n",
       "      <td>606.0</td>\n",
       "      <td>148517.0</td>\n",
       "      <td>10095.0</td>\n",
       "      <td>5.54</td>\n",
       "      <td>10095.0</td>\n",
       "      <td>446.9</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10391.0</td>\n",
       "      <td>54033.0</td>\n",
       "      <td>5164.0</td>\n",
       "      <td>277.0</td>\n",
       "      <td>25719.0</td>\n",
       "      <td>4685.0</td>\n",
       "      <td>32437.0</td>\n",
       "      <td>138422.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20000331</td>\n",
       "      <td>AXP</td>\n",
       "      <td>920.0</td>\n",
       "      <td>6021.0</td>\n",
       "      <td>656.0</td>\n",
       "      <td>150662.0</td>\n",
       "      <td>10253.0</td>\n",
       "      <td>1.48</td>\n",
       "      <td>10253.0</td>\n",
       "      <td>444.7</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7425.0</td>\n",
       "      <td>53663.0</td>\n",
       "      <td>5101.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>26379.0</td>\n",
       "      <td>5670.0</td>\n",
       "      <td>29342.0</td>\n",
       "      <td>140409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20000630</td>\n",
       "      <td>AXP</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>6370.0</td>\n",
       "      <td>740.0</td>\n",
       "      <td>148553.0</td>\n",
       "      <td>10509.0</td>\n",
       "      <td>1.05</td>\n",
       "      <td>10509.0</td>\n",
       "      <td>1333.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6841.0</td>\n",
       "      <td>54286.0</td>\n",
       "      <td>5324.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>29536.0</td>\n",
       "      <td>5336.0</td>\n",
       "      <td>26170.0</td>\n",
       "      <td>138044.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-6add0f15-8cc9-473e-924f-79db07ca70e5')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-6add0f15-8cc9-473e-924f-79db07ca70e5 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-6add0f15-8cc9-473e-924f-79db07ca70e5');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "       date  tic  op_inc_q   rev_q  net_inc_q  tot_assets  sh_equity  \\\n",
       "0  19990630  AXP     896.0  5564.0      646.0    132452.0     9762.0   \n",
       "1  19990930  AXP     906.0  5584.0      648.0    132616.0     9744.0   \n",
       "2  19991231  AXP     845.0  6009.0      606.0    148517.0    10095.0   \n",
       "3  20000331  AXP     920.0  6021.0      656.0    150662.0    10253.0   \n",
       "4  20000630  AXP    1046.0  6370.0      740.0    148553.0    10509.0   \n",
       "\n",
       "   eps_incl_ex   com_eq  sh_outstanding  ...  cur_assets  cur_liabilities  \\\n",
       "0         2.73   9762.0           449.0  ...         NaN              NaN   \n",
       "1         4.18   9744.0           447.6  ...         NaN              NaN   \n",
       "2         5.54  10095.0           446.9  ...         NaN              NaN   \n",
       "3         1.48  10253.0           444.7  ...         NaN              NaN   \n",
       "4         1.05  10509.0          1333.0  ...         NaN              NaN   \n",
       "\n",
       "   cash_eq  receivables  cogs_q  inventories  payables  long_debt  short_debt  \\\n",
       "0   6096.0      46774.0  4668.0        448.0   22282.0     7005.0     24785.0   \n",
       "1   5102.0      48827.0  4678.0        284.0   23587.0     6720.0     24683.0   \n",
       "2  10391.0      54033.0  5164.0        277.0   25719.0     4685.0     32437.0   \n",
       "3   7425.0      53663.0  5101.0        315.0   26379.0     5670.0     29342.0   \n",
       "4   6841.0      54286.0  5324.0        261.0   29536.0     5336.0     26170.0   \n",
       "\n",
       "   tot_liabilities  \n",
       "0         122690.0  \n",
       "1         122872.0  \n",
       "2         138422.0  \n",
       "3         140409.0  \n",
       "4         138044.0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the data\n",
    "fund_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xPvwtMQUqZdP"
   },
   "source": [
    "## 4.3 Calculate financial ratios\n",
    "- For items from Profit/Loss statements, we calculate LTM (Last Twelve Months) and use them to derive profitability related ratios such as Operating Maring and ROE. For items from balance sheets, we use the numbers on the day.\n",
    "- To check the definitions of the financial ratios calculated here, please refer to CFI's website: https://corporatefinanceinstitute.com/resources/knowledge/finance/financial-ratios/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "cfWtEophqS33",
    "outputId": "eed30ddb-d3d6-4479-8253-bc099544d50c"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:15: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  from ipykernel import kernelapp as app\n",
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:15: RuntimeWarning: invalid value encountered in double_scalars\n",
      "  from ipykernel import kernelapp as app\n",
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:25: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:25: RuntimeWarning: invalid value encountered in double_scalars\n",
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:77: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:77: RuntimeWarning: invalid value encountered in double_scalars\n"
     ]
    }
   ],
   "source": [
    "# Calculate financial ratios\n",
    "date = pd.to_datetime(fund_data['date'],format='%Y%m%d')\n",
    "\n",
    "tic = fund_data['tic'].to_frame('tic')\n",
    "\n",
    "# Profitability ratios\n",
    "# Operating Margin\n",
    "OPM = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='OPM')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        OPM[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        OPM.iloc[i] = np.nan\n",
    "    else:\n",
    "        OPM.iloc[i] = np.sum(fund_data['op_inc_q'].iloc[i-3:i])/np.sum(fund_data['rev_q'].iloc[i-3:i])\n",
    "\n",
    "# Net Profit Margin        \n",
    "NPM = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='NPM')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        NPM[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        NPM.iloc[i] = np.nan\n",
    "    else:\n",
    "        NPM.iloc[i] = np.sum(fund_data['net_inc_q'].iloc[i-3:i])/np.sum(fund_data['rev_q'].iloc[i-3:i])\n",
    "\n",
    "# Return On Assets\n",
    "ROA = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='ROA')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        ROA[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        ROA.iloc[i] = np.nan\n",
    "    else:\n",
    "        ROA.iloc[i] = np.sum(fund_data['net_inc_q'].iloc[i-3:i])/fund_data['tot_assets'].iloc[i]\n",
    "\n",
    "# Return on Equity\n",
    "ROE = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='ROE')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        ROE[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        ROE.iloc[i] = np.nan\n",
    "    else:\n",
    "        ROE.iloc[i] = np.sum(fund_data['net_inc_q'].iloc[i-3:i])/fund_data['sh_equity'].iloc[i]        \n",
    "\n",
    "# For calculating valuation ratios in the next subpart, calculate per share items in advance\n",
    "# Earnings Per Share       \n",
    "EPS = fund_data['eps_incl_ex'].to_frame('EPS')\n",
    "\n",
    "# Book Per Share\n",
    "BPS = (fund_data['com_eq']/fund_data['sh_outstanding']).to_frame('BPS') # Need to check units\n",
    "\n",
    "#Dividend Per Share\n",
    "DPS = fund_data['div_per_sh'].to_frame('DPS')\n",
    "\n",
    "# Liquidity ratios\n",
    "# Current ratio\n",
    "cur_ratio = (fund_data['cur_assets']/fund_data['cur_liabilities']).to_frame('cur_ratio')\n",
    "\n",
    "# Quick ratio\n",
    "quick_ratio = ((fund_data['cash_eq'] + fund_data['receivables'] )/fund_data['cur_liabilities']).to_frame('quick_ratio')\n",
    "\n",
    "# Cash ratio\n",
    "cash_ratio = (fund_data['cash_eq']/fund_data['cur_liabilities']).to_frame('cash_ratio')\n",
    "\n",
    "\n",
    "# Efficiency ratios\n",
    "# Inventory turnover ratio\n",
    "inv_turnover = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='inv_turnover')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        inv_turnover[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        inv_turnover.iloc[i] = np.nan\n",
    "    else:\n",
    "        inv_turnover.iloc[i] = np.sum(fund_data['cogs_q'].iloc[i-3:i])/fund_data['inventories'].iloc[i]\n",
    "\n",
    "# Receivables turnover ratio       \n",
    "acc_rec_turnover = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='acc_rec_turnover')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        acc_rec_turnover[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        acc_rec_turnover.iloc[i] = np.nan\n",
    "    else:\n",
    "        acc_rec_turnover.iloc[i] = np.sum(fund_data['rev_q'].iloc[i-3:i])/fund_data['receivables'].iloc[i]\n",
    "\n",
    "# Payable turnover ratio\n",
    "acc_pay_turnover = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='acc_pay_turnover')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        acc_pay_turnover[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        acc_pay_turnover.iloc[i] = np.nan\n",
    "    else:\n",
    "        acc_pay_turnover.iloc[i] = np.sum(fund_data['cogs_q'].iloc[i-3:i])/fund_data['payables'].iloc[i]\n",
    "        \n",
    "## Leverage financial ratios\n",
    "# Debt ratio\n",
    "debt_ratio = (fund_data['tot_liabilities']/fund_data['tot_assets']).to_frame('debt_ratio')\n",
    "\n",
    "# Debt to Equity ratio\n",
    "debt_to_equity = (fund_data['tot_liabilities']/fund_data['sh_equity']).to_frame('debt_to_equity')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "wwFVopRDqcby"
   },
   "outputs": [],
   "source": [
    "# Create a dataframe that merges all the ratios\n",
    "ratios = pd.concat([date,tic,OPM,NPM,ROA,ROE,EPS,BPS,DPS,\n",
    "                    cur_ratio,quick_ratio,cash_ratio,inv_turnover,acc_rec_turnover,acc_pay_turnover,\n",
    "                   debt_ratio,debt_to_equity], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "Mvnw7izFsJcT",
    "outputId": "283555af-71f7-468f-84c4-f35d9e04e705"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-9bd07bf8-4a44-4c8d-9cf4-4bfd9717db46\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "      <th>EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>DPS</th>\n",
       "      <th>cur_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999-06-30</td>\n",
       "      <td>AXP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.73</td>\n",
       "      <td>21.741648</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.926298</td>\n",
       "      <td>12.568121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>AXP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.18</td>\n",
       "      <td>21.769437</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.926525</td>\n",
       "      <td>12.610016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>AXP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.54</td>\n",
       "      <td>22.588946</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.932028</td>\n",
       "      <td>13.711937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000-03-31</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.154281</td>\n",
       "      <td>0.110742</td>\n",
       "      <td>0.012611</td>\n",
       "      <td>0.185312</td>\n",
       "      <td>1.48</td>\n",
       "      <td>23.055993</td>\n",
       "      <td>0.225</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.063492</td>\n",
       "      <td>0.319717</td>\n",
       "      <td>0.550059</td>\n",
       "      <td>0.931947</td>\n",
       "      <td>13.694431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-06-30</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.151641</td>\n",
       "      <td>0.108436</td>\n",
       "      <td>0.012857</td>\n",
       "      <td>0.181749</td>\n",
       "      <td>1.05</td>\n",
       "      <td>7.883721</td>\n",
       "      <td>0.080</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57.252874</td>\n",
       "      <td>0.324467</td>\n",
       "      <td>0.505925</td>\n",
       "      <td>0.929258</td>\n",
       "      <td>13.135788</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9bd07bf8-4a44-4c8d-9cf4-4bfd9717db46')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-9bd07bf8-4a44-4c8d-9cf4-4bfd9717db46 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-9bd07bf8-4a44-4c8d-9cf4-4bfd9717db46');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        date  tic       OPM       NPM       ROA       ROE   EPS        BPS  \\\n",
       "0 1999-06-30  AXP       NaN       NaN       NaN       NaN  2.73  21.741648   \n",
       "1 1999-09-30  AXP       NaN       NaN       NaN       NaN  4.18  21.769437   \n",
       "2 1999-12-31  AXP       NaN       NaN       NaN       NaN  5.54  22.588946   \n",
       "3 2000-03-31  AXP  0.154281  0.110742  0.012611  0.185312  1.48  23.055993   \n",
       "4 2000-06-30  AXP  0.151641  0.108436  0.012857  0.181749  1.05   7.883721   \n",
       "\n",
       "     DPS  cur_ratio  quick_ratio  cash_ratio inv_turnover acc_rec_turnover  \\\n",
       "0  0.225        NaN          NaN         NaN          NaN              NaN   \n",
       "1  0.225        NaN          NaN         NaN          NaN              NaN   \n",
       "2  0.225        NaN          NaN         NaN          NaN              NaN   \n",
       "3  0.225        NaN          NaN         NaN    46.063492         0.319717   \n",
       "4  0.080        NaN          NaN         NaN    57.252874         0.324467   \n",
       "\n",
       "  acc_pay_turnover  debt_ratio  debt_to_equity  \n",
       "0              NaN    0.926298       12.568121  \n",
       "1              NaN    0.926525       12.610016  \n",
       "2              NaN    0.932028       13.711937  \n",
       "3         0.550059    0.931947       13.694431  \n",
       "4         0.505925    0.929258       13.135788  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the ratio data\n",
    "ratios.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "AvG67ouguUKF",
    "outputId": "98f4a73c-94af-4fa1-f9ba-30b410995161"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-1a131e56-0dad-4a71-8ee9-d08a22caa925\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "      <th>EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>DPS</th>\n",
       "      <th>cur_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2451</th>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>V</td>\n",
       "      <td>0.667517</td>\n",
       "      <td>0.521213</td>\n",
       "      <td>0.129058</td>\n",
       "      <td>0.271736</td>\n",
       "      <td>2.85</td>\n",
       "      <td>13.647142</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.248714</td>\n",
       "      <td>1.140070</td>\n",
       "      <td>0.955150</td>\n",
       "      <td>inf</td>\n",
       "      <td>6.11635</td>\n",
       "      <td>2.697537</td>\n",
       "      <td>0.525062</td>\n",
       "      <td>1.105537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2452</th>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>V</td>\n",
       "      <td>0.668385</td>\n",
       "      <td>0.519867</td>\n",
       "      <td>0.120448</td>\n",
       "      <td>0.264075</td>\n",
       "      <td>3.92</td>\n",
       "      <td>14.203947</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.553478</td>\n",
       "      <td>1.443292</td>\n",
       "      <td>1.221925</td>\n",
       "      <td>inf</td>\n",
       "      <td>5.063131</td>\n",
       "      <td>1.889507</td>\n",
       "      <td>0.543886</td>\n",
       "      <td>1.192433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2453</th>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>V</td>\n",
       "      <td>0.654464</td>\n",
       "      <td>0.52129</td>\n",
       "      <td>0.107873</td>\n",
       "      <td>0.241066</td>\n",
       "      <td>4.90</td>\n",
       "      <td>14.653484</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.905238</td>\n",
       "      <td>1.784838</td>\n",
       "      <td>1.579807</td>\n",
       "      <td>inf</td>\n",
       "      <td>5.628571</td>\n",
       "      <td>2.730366</td>\n",
       "      <td>0.552515</td>\n",
       "      <td>1.234714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2454</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>V</td>\n",
       "      <td>0.638994</td>\n",
       "      <td>0.480876</td>\n",
       "      <td>0.094422</td>\n",
       "      <td>0.201545</td>\n",
       "      <td>1.42</td>\n",
       "      <td>15.908283</td>\n",
       "      <td>0.32</td>\n",
       "      <td>2.121065</td>\n",
       "      <td>1.969814</td>\n",
       "      <td>1.700081</td>\n",
       "      <td>inf</td>\n",
       "      <td>4.725314</td>\n",
       "      <td>2.347866</td>\n",
       "      <td>0.531507</td>\n",
       "      <td>1.134505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2455</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>V</td>\n",
       "      <td>0.640128</td>\n",
       "      <td>0.488704</td>\n",
       "      <td>0.095218</td>\n",
       "      <td>0.202568</td>\n",
       "      <td>2.80</td>\n",
       "      <td>16.088525</td>\n",
       "      <td>0.32</td>\n",
       "      <td>2.116356</td>\n",
       "      <td>1.954292</td>\n",
       "      <td>1.700574</td>\n",
       "      <td>inf</td>\n",
       "      <td>4.844961</td>\n",
       "      <td>2.367357</td>\n",
       "      <td>0.529946</td>\n",
       "      <td>1.127414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1a131e56-0dad-4a71-8ee9-d08a22caa925')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-1a131e56-0dad-4a71-8ee9-d08a22caa925 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-1a131e56-0dad-4a71-8ee9-d08a22caa925');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "           date tic       OPM       NPM       ROA       ROE   EPS        BPS  \\\n",
       "2451 2020-03-31   V  0.667517  0.521213  0.129058  0.271736  2.85  13.647142   \n",
       "2452 2020-06-30   V  0.668385  0.519867  0.120448  0.264075  3.92  14.203947   \n",
       "2453 2020-09-30   V  0.654464   0.52129  0.107873  0.241066  4.90  14.653484   \n",
       "2454 2020-12-31   V  0.638994  0.480876  0.094422  0.201545  1.42  15.908283   \n",
       "2455 2021-03-31   V  0.640128  0.488704  0.095218  0.202568  2.80  16.088525   \n",
       "\n",
       "       DPS  cur_ratio  quick_ratio  cash_ratio inv_turnover acc_rec_turnover  \\\n",
       "2451  0.30   1.248714     1.140070    0.955150          inf          6.11635   \n",
       "2452  0.30   1.553478     1.443292    1.221925          inf         5.063131   \n",
       "2453  0.30   1.905238     1.784838    1.579807          inf         5.628571   \n",
       "2454  0.32   2.121065     1.969814    1.700081          inf         4.725314   \n",
       "2455  0.32   2.116356     1.954292    1.700574          inf         4.844961   \n",
       "\n",
       "     acc_pay_turnover  debt_ratio  debt_to_equity  \n",
       "2451         2.697537    0.525062        1.105537  \n",
       "2452         1.889507    0.543886        1.192433  \n",
       "2453         2.730366    0.552515        1.234714  \n",
       "2454         2.347866    0.531507        1.134505  \n",
       "2455         2.367357    0.529946        1.127414  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratios.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "JULhnNv8uaOB"
   },
   "source": [
    "## 4.4 Deal with NAs and infinite values\n",
    "- We replace N/A and infinite values with zero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "id": "nuKPlGe4sNzQ"
   },
   "outputs": [],
   "source": [
    "# Replace NAs infinite values with zero\n",
    "final_ratios = ratios.copy()\n",
    "final_ratios = final_ratios.fillna(0)\n",
    "final_ratios = final_ratios.replace(np.inf,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "wc_rvvm1sRDd",
    "outputId": "65d0456d-c8d7-441b-9981-49eff7a8eb95"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-cb62ccc5-805c-4461-8b58-0d7572007119\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "      <th>EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>DPS</th>\n",
       "      <th>cur_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999-06-30</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.73</td>\n",
       "      <td>21.741648</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.926298</td>\n",
       "      <td>12.568121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.18</td>\n",
       "      <td>21.769437</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.926525</td>\n",
       "      <td>12.610016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.54</td>\n",
       "      <td>22.588946</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.932028</td>\n",
       "      <td>13.711937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000-03-31</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.154281</td>\n",
       "      <td>0.110742</td>\n",
       "      <td>0.012611</td>\n",
       "      <td>0.185312</td>\n",
       "      <td>1.48</td>\n",
       "      <td>23.055993</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.063492</td>\n",
       "      <td>0.319717</td>\n",
       "      <td>0.550059</td>\n",
       "      <td>0.931947</td>\n",
       "      <td>13.694431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-06-30</td>\n",
       "      <td>AXP</td>\n",
       "      <td>0.151641</td>\n",
       "      <td>0.108436</td>\n",
       "      <td>0.012857</td>\n",
       "      <td>0.181749</td>\n",
       "      <td>1.05</td>\n",
       "      <td>7.883721</td>\n",
       "      <td>0.080</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>57.252874</td>\n",
       "      <td>0.324467</td>\n",
       "      <td>0.505925</td>\n",
       "      <td>0.929258</td>\n",
       "      <td>13.135788</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-cb62ccc5-805c-4461-8b58-0d7572007119')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-cb62ccc5-805c-4461-8b58-0d7572007119 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-cb62ccc5-805c-4461-8b58-0d7572007119');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        date  tic       OPM       NPM       ROA       ROE   EPS        BPS  \\\n",
       "0 1999-06-30  AXP  0.000000  0.000000  0.000000  0.000000  2.73  21.741648   \n",
       "1 1999-09-30  AXP  0.000000  0.000000  0.000000  0.000000  4.18  21.769437   \n",
       "2 1999-12-31  AXP  0.000000  0.000000  0.000000  0.000000  5.54  22.588946   \n",
       "3 2000-03-31  AXP  0.154281  0.110742  0.012611  0.185312  1.48  23.055993   \n",
       "4 2000-06-30  AXP  0.151641  0.108436  0.012857  0.181749  1.05   7.883721   \n",
       "\n",
       "     DPS  cur_ratio  quick_ratio  cash_ratio  inv_turnover  acc_rec_turnover  \\\n",
       "0  0.225        0.0          0.0         0.0      0.000000          0.000000   \n",
       "1  0.225        0.0          0.0         0.0      0.000000          0.000000   \n",
       "2  0.225        0.0          0.0         0.0      0.000000          0.000000   \n",
       "3  0.225        0.0          0.0         0.0     46.063492          0.319717   \n",
       "4  0.080        0.0          0.0         0.0     57.252874          0.324467   \n",
       "\n",
       "   acc_pay_turnover  debt_ratio  debt_to_equity  \n",
       "0          0.000000    0.926298       12.568121  \n",
       "1          0.000000    0.926525       12.610016  \n",
       "2          0.000000    0.932028       13.711937  \n",
       "3          0.550059    0.931947       13.694431  \n",
       "4          0.505925    0.929258       13.135788  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "RKwmRfs5sfra",
    "outputId": "3f0b7d81-0057-4f0e-db42-9af9e89f6ff6"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-2ee2d487-8230-435a-84d0-bc3e459949d0\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "      <th>EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>DPS</th>\n",
       "      <th>cur_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2451</th>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>V</td>\n",
       "      <td>0.667517</td>\n",
       "      <td>0.521213</td>\n",
       "      <td>0.129058</td>\n",
       "      <td>0.271736</td>\n",
       "      <td>2.85</td>\n",
       "      <td>13.647142</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.248714</td>\n",
       "      <td>1.140070</td>\n",
       "      <td>0.955150</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.116350</td>\n",
       "      <td>2.697537</td>\n",
       "      <td>0.525062</td>\n",
       "      <td>1.105537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2452</th>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>V</td>\n",
       "      <td>0.668385</td>\n",
       "      <td>0.519867</td>\n",
       "      <td>0.120448</td>\n",
       "      <td>0.264075</td>\n",
       "      <td>3.92</td>\n",
       "      <td>14.203947</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.553478</td>\n",
       "      <td>1.443292</td>\n",
       "      <td>1.221925</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.063131</td>\n",
       "      <td>1.889507</td>\n",
       "      <td>0.543886</td>\n",
       "      <td>1.192433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2453</th>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>V</td>\n",
       "      <td>0.654464</td>\n",
       "      <td>0.521290</td>\n",
       "      <td>0.107873</td>\n",
       "      <td>0.241066</td>\n",
       "      <td>4.90</td>\n",
       "      <td>14.653484</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.905238</td>\n",
       "      <td>1.784838</td>\n",
       "      <td>1.579807</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.628571</td>\n",
       "      <td>2.730366</td>\n",
       "      <td>0.552515</td>\n",
       "      <td>1.234714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2454</th>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>V</td>\n",
       "      <td>0.638994</td>\n",
       "      <td>0.480876</td>\n",
       "      <td>0.094422</td>\n",
       "      <td>0.201545</td>\n",
       "      <td>1.42</td>\n",
       "      <td>15.908283</td>\n",
       "      <td>0.32</td>\n",
       "      <td>2.121065</td>\n",
       "      <td>1.969814</td>\n",
       "      <td>1.700081</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.725314</td>\n",
       "      <td>2.347866</td>\n",
       "      <td>0.531507</td>\n",
       "      <td>1.134505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2455</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>V</td>\n",
       "      <td>0.640128</td>\n",
       "      <td>0.488704</td>\n",
       "      <td>0.095218</td>\n",
       "      <td>0.202568</td>\n",
       "      <td>2.80</td>\n",
       "      <td>16.088525</td>\n",
       "      <td>0.32</td>\n",
       "      <td>2.116356</td>\n",
       "      <td>1.954292</td>\n",
       "      <td>1.700574</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.844961</td>\n",
       "      <td>2.367357</td>\n",
       "      <td>0.529946</td>\n",
       "      <td>1.127414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2ee2d487-8230-435a-84d0-bc3e459949d0')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-2ee2d487-8230-435a-84d0-bc3e459949d0 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-2ee2d487-8230-435a-84d0-bc3e459949d0');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "           date tic       OPM       NPM       ROA       ROE   EPS        BPS  \\\n",
       "2451 2020-03-31   V  0.667517  0.521213  0.129058  0.271736  2.85  13.647142   \n",
       "2452 2020-06-30   V  0.668385  0.519867  0.120448  0.264075  3.92  14.203947   \n",
       "2453 2020-09-30   V  0.654464  0.521290  0.107873  0.241066  4.90  14.653484   \n",
       "2454 2020-12-31   V  0.638994  0.480876  0.094422  0.201545  1.42  15.908283   \n",
       "2455 2021-03-31   V  0.640128  0.488704  0.095218  0.202568  2.80  16.088525   \n",
       "\n",
       "       DPS  cur_ratio  quick_ratio  cash_ratio  inv_turnover  \\\n",
       "2451  0.30   1.248714     1.140070    0.955150           0.0   \n",
       "2452  0.30   1.553478     1.443292    1.221925           0.0   \n",
       "2453  0.30   1.905238     1.784838    1.579807           0.0   \n",
       "2454  0.32   2.121065     1.969814    1.700081           0.0   \n",
       "2455  0.32   2.116356     1.954292    1.700574           0.0   \n",
       "\n",
       "      acc_rec_turnover  acc_pay_turnover  debt_ratio  debt_to_equity  \n",
       "2451          6.116350          2.697537    0.525062        1.105537  \n",
       "2452          5.063131          1.889507    0.543886        1.192433  \n",
       "2453          5.628571          2.730366    0.552515        1.234714  \n",
       "2454          4.725314          2.347866    0.531507        1.134505  \n",
       "2455          4.844961          2.367357    0.529946        1.127414  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "66kjM0lhu91F"
   },
   "source": [
    "## 4.5 Merge stock price data and ratios into one dataframe\n",
    "- Merge the price dataframe preprocessed in Part 3 and the ratio dataframe created in this part\n",
    "- Since the prices are daily and ratios are quartely, we have NAs in the ratio columns after merging the two dataframes. We deal with this by backfilling the ratios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "id": "Kixon2tR3RLT"
   },
   "outputs": [],
   "source": [
    "list_ticker = df[\"tic\"].unique().tolist()\n",
    "list_date = list(pd.date_range(df['date'].min(),df['date'].max()))\n",
    "combination = list(itertools.product(list_date,list_ticker))\n",
    "\n",
    "# Merge stock price data and ratios into one dataframe\n",
    "processed_full = pd.DataFrame(combination,columns=[\"date\",\"tic\"]).merge(df,on=[\"date\",\"tic\"],how=\"left\")\n",
    "processed_full = processed_full.merge(final_ratios,how='left',on=['date','tic'])\n",
    "processed_full = processed_full.sort_values(['tic','date'])\n",
    "\n",
    "# Backfill the ratio data to make them daily\n",
    "processed_full = processed_full.bfill(axis='rows')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CGU69Ccfw_bR"
   },
   "source": [
    "## 4.6 Calculate market valuation ratios using daily stock price data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "id": "EhiYLZPBVZNW"
   },
   "outputs": [],
   "source": [
    "# Calculate P/E, P/B and dividend yield using daily closing price\n",
    "processed_full['PE'] = processed_full['close']/processed_full['EPS']\n",
    "processed_full['PB'] = processed_full['close']/processed_full['BPS']\n",
    "processed_full['Div_yield'] = processed_full['DPS']/processed_full['close']\n",
    "\n",
    "# Drop per share items used for the above calculation\n",
    "processed_full = processed_full.drop(columns=['day','EPS','BPS','DPS'])\n",
    "# Replace NAs infinite values with zero\n",
    "processed_full = processed_full.copy()\n",
    "processed_full = processed_full.fillna(0)\n",
    "processed_full = processed_full.replace(np.inf,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 630
    },
    "id": "grvhGJJII3Xn",
    "outputId": "3f217d2a-050f-46af-86b8-7eb8a8e02e35"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-3a4fd0d1-0249-416a-9e1d-8053e1e289cc\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>...</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "      <th>PE</th>\n",
       "      <th>PB</th>\n",
       "      <th>Div_yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>3.067143</td>\n",
       "      <td>3.251429</td>\n",
       "      <td>3.041429</td>\n",
       "      <td>2.767330</td>\n",
       "      <td>746015200.0</td>\n",
       "      <td>0.217886</td>\n",
       "      <td>0.163846</td>\n",
       "      <td>0.103222</td>\n",
       "      <td>...</td>\n",
       "      <td>2.039779</td>\n",
       "      <td>1.818995</td>\n",
       "      <td>54.403846</td>\n",
       "      <td>8.972003</td>\n",
       "      <td>4.269115</td>\n",
       "      <td>0.437727</td>\n",
       "      <td>0.778495</td>\n",
       "      <td>0.636168</td>\n",
       "      <td>0.101527</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>AMGN</td>\n",
       "      <td>58.590000</td>\n",
       "      <td>59.080002</td>\n",
       "      <td>57.750000</td>\n",
       "      <td>44.523743</td>\n",
       "      <td>6547900.0</td>\n",
       "      <td>0.093973</td>\n",
       "      <td>0.072040</td>\n",
       "      <td>0.014094</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.351354</td>\n",
       "      <td>0.653355</td>\n",
       "      <td>0.869784</td>\n",
       "      <td>6.679531</td>\n",
       "      <td>143.624976</td>\n",
       "      <td>4.123353</td>\n",
       "      <td>0.004043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>AXP</td>\n",
       "      <td>18.570000</td>\n",
       "      <td>19.520000</td>\n",
       "      <td>18.400000</td>\n",
       "      <td>15.477424</td>\n",
       "      <td>10955700.0</td>\n",
       "      <td>0.093973</td>\n",
       "      <td>0.072040</td>\n",
       "      <td>0.014094</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.351354</td>\n",
       "      <td>0.653355</td>\n",
       "      <td>0.869784</td>\n",
       "      <td>6.679531</td>\n",
       "      <td>49.927173</td>\n",
       "      <td>1.433367</td>\n",
       "      <td>0.011630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>BA</td>\n",
       "      <td>42.799999</td>\n",
       "      <td>45.560001</td>\n",
       "      <td>42.779999</td>\n",
       "      <td>33.941097</td>\n",
       "      <td>7010200.0</td>\n",
       "      <td>0.047307</td>\n",
       "      <td>0.032525</td>\n",
       "      <td>0.026400</td>\n",
       "      <td>...</td>\n",
       "      <td>0.368463</td>\n",
       "      <td>0.148507</td>\n",
       "      <td>2.329670</td>\n",
       "      <td>6.815203</td>\n",
       "      <td>2.076967</td>\n",
       "      <td>1.009198</td>\n",
       "      <td>-109.722986</td>\n",
       "      <td>39.012755</td>\n",
       "      <td>-35.751050</td>\n",
       "      <td>0.012374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>CAT</td>\n",
       "      <td>44.910000</td>\n",
       "      <td>46.980000</td>\n",
       "      <td>44.709999</td>\n",
       "      <td>31.942245</td>\n",
       "      <td>7117200.0</td>\n",
       "      <td>0.124545</td>\n",
       "      <td>0.066662</td>\n",
       "      <td>0.040891</td>\n",
       "      <td>...</td>\n",
       "      <td>0.890488</td>\n",
       "      <td>0.163158</td>\n",
       "      <td>3.540791</td>\n",
       "      <td>2.460351</td>\n",
       "      <td>8.472455</td>\n",
       "      <td>0.893715</td>\n",
       "      <td>9.089489</td>\n",
       "      <td>-168.117081</td>\n",
       "      <td>3.083088</td>\n",
       "      <td>0.013149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>CRM</td>\n",
       "      <td>8.025000</td>\n",
       "      <td>8.550000</td>\n",
       "      <td>7.912500</td>\n",
       "      <td>8.505000</td>\n",
       "      <td>4069200.0</td>\n",
       "      <td>0.234698</td>\n",
       "      <td>0.196418</td>\n",
       "      <td>0.097593</td>\n",
       "      <td>...</td>\n",
       "      <td>2.498162</td>\n",
       "      <td>2.170759</td>\n",
       "      <td>9.054201</td>\n",
       "      <td>6.844634</td>\n",
       "      <td>16.036800</td>\n",
       "      <td>0.400215</td>\n",
       "      <td>0.667591</td>\n",
       "      <td>13.500000</td>\n",
       "      <td>1.351255</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>CSCO</td>\n",
       "      <td>16.410000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>16.250000</td>\n",
       "      <td>12.155674</td>\n",
       "      <td>40980600.0</td>\n",
       "      <td>0.234698</td>\n",
       "      <td>0.196418</td>\n",
       "      <td>0.097593</td>\n",
       "      <td>...</td>\n",
       "      <td>2.498162</td>\n",
       "      <td>2.170759</td>\n",
       "      <td>9.054201</td>\n",
       "      <td>6.844634</td>\n",
       "      <td>16.036800</td>\n",
       "      <td>0.400215</td>\n",
       "      <td>0.667591</td>\n",
       "      <td>19.294721</td>\n",
       "      <td>1.931266</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>CVX</td>\n",
       "      <td>74.230003</td>\n",
       "      <td>77.300003</td>\n",
       "      <td>73.580002</td>\n",
       "      <td>44.404167</td>\n",
       "      <td>13695900.0</td>\n",
       "      <td>0.141417</td>\n",
       "      <td>0.097223</td>\n",
       "      <td>0.117691</td>\n",
       "      <td>...</td>\n",
       "      <td>0.952878</td>\n",
       "      <td>0.373760</td>\n",
       "      <td>23.920348</td>\n",
       "      <td>13.387209</td>\n",
       "      <td>11.276861</td>\n",
       "      <td>0.449174</td>\n",
       "      <td>0.815455</td>\n",
       "      <td>48.265399</td>\n",
       "      <td>1.019502</td>\n",
       "      <td>0.014638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>DIS</td>\n",
       "      <td>22.760000</td>\n",
       "      <td>24.030001</td>\n",
       "      <td>22.500000</td>\n",
       "      <td>20.597496</td>\n",
       "      <td>9796600.0</td>\n",
       "      <td>0.167221</td>\n",
       "      <td>0.102157</td>\n",
       "      <td>0.045834</td>\n",
       "      <td>...</td>\n",
       "      <td>0.815629</td>\n",
       "      <td>0.330748</td>\n",
       "      <td>11.310223</td>\n",
       "      <td>5.725855</td>\n",
       "      <td>4.287167</td>\n",
       "      <td>0.455848</td>\n",
       "      <td>0.837721</td>\n",
       "      <td>26.072780</td>\n",
       "      <td>1.126511</td>\n",
       "      <td>0.016992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>DOW</td>\n",
       "      <td>52.750000</td>\n",
       "      <td>53.500000</td>\n",
       "      <td>49.500000</td>\n",
       "      <td>41.373936</td>\n",
       "      <td>2350800.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>179.886677</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 22 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3a4fd0d1-0249-416a-9e1d-8053e1e289cc')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-3a4fd0d1-0249-416a-9e1d-8053e1e289cc button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-3a4fd0d1-0249-416a-9e1d-8053e1e289cc');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        date   tic       open       high        low      close       volume  \\\n",
       "0 2009-01-02  AAPL   3.067143   3.251429   3.041429   2.767330  746015200.0   \n",
       "1 2009-01-02  AMGN  58.590000  59.080002  57.750000  44.523743    6547900.0   \n",
       "2 2009-01-02   AXP  18.570000  19.520000  18.400000  15.477424   10955700.0   \n",
       "3 2009-01-02    BA  42.799999  45.560001  42.779999  33.941097    7010200.0   \n",
       "4 2009-01-02   CAT  44.910000  46.980000  44.709999  31.942245    7117200.0   \n",
       "5 2009-01-02   CRM   8.025000   8.550000   7.912500   8.505000    4069200.0   \n",
       "6 2009-01-02  CSCO  16.410000  17.000000  16.250000  12.155674   40980600.0   \n",
       "7 2009-01-02   CVX  74.230003  77.300003  73.580002  44.404167   13695900.0   \n",
       "8 2009-01-02   DIS  22.760000  24.030001  22.500000  20.597496    9796600.0   \n",
       "9 2009-01-02   DOW  52.750000  53.500000  49.500000  41.373936    2350800.0   \n",
       "\n",
       "        OPM       NPM       ROA  ...  quick_ratio  cash_ratio  inv_turnover  \\\n",
       "0  0.217886  0.163846  0.103222  ...     2.039779    1.818995     54.403846   \n",
       "1  0.093973  0.072040  0.014094  ...     0.000000    0.000000      0.000000   \n",
       "2  0.093973  0.072040  0.014094  ...     0.000000    0.000000      0.000000   \n",
       "3  0.047307  0.032525  0.026400  ...     0.368463    0.148507      2.329670   \n",
       "4  0.124545  0.066662  0.040891  ...     0.890488    0.163158      3.540791   \n",
       "5  0.234698  0.196418  0.097593  ...     2.498162    2.170759      9.054201   \n",
       "6  0.234698  0.196418  0.097593  ...     2.498162    2.170759      9.054201   \n",
       "7  0.141417  0.097223  0.117691  ...     0.952878    0.373760     23.920348   \n",
       "8  0.167221  0.102157  0.045834  ...     0.815629    0.330748     11.310223   \n",
       "9  0.000000  0.000000  0.000000  ...     0.000000    0.000000      0.000000   \n",
       "\n",
       "   acc_rec_turnover  acc_pay_turnover  debt_ratio  debt_to_equity          PE  \\\n",
       "0          8.972003          4.269115    0.437727        0.778495    0.636168   \n",
       "1          0.351354          0.653355    0.869784        6.679531  143.624976   \n",
       "2          0.351354          0.653355    0.869784        6.679531   49.927173   \n",
       "3          6.815203          2.076967    1.009198     -109.722986   39.012755   \n",
       "4          2.460351          8.472455    0.893715        9.089489 -168.117081   \n",
       "5          6.844634         16.036800    0.400215        0.667591   13.500000   \n",
       "6          6.844634         16.036800    0.400215        0.667591   19.294721   \n",
       "7         13.387209         11.276861    0.449174        0.815455   48.265399   \n",
       "8          5.725855          4.287167    0.455848        0.837721   26.072780   \n",
       "9          0.000000          0.000000    0.000000        0.000000  179.886677   \n",
       "\n",
       "          PB  Div_yield  \n",
       "0   0.101527   0.000000  \n",
       "1   4.123353   0.004043  \n",
       "2   1.433367   0.011630  \n",
       "3 -35.751050   0.012374  \n",
       "4   3.083088   0.013149  \n",
       "5   1.351255   0.000000  \n",
       "6   1.931266   0.000000  \n",
       "7   1.019502   0.014638  \n",
       "8   1.126511   0.016992  \n",
       "9   0.000000   0.000000  \n",
       "\n",
       "[10 rows x 22 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the final data\n",
    "processed_full.sort_values(['date','tic'],ignore_index=True).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-QsYaY0Dh1iw"
   },
   "source": [
    "<a id='4'></a>\n",
    "# Part 5. A Market Environment in OpenAI Gym-style\n",
    "The training process involves observing stock price change, taking an action and reward's calculation. By interacting with the market environment, the agent will eventually derive a trading strategy that may maximize (expected) rewards.\n",
    "\n",
    "Our market environment, based on OpenAI Gym, simulates stock markets with historical market data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5TOhcryx44bb"
   },
   "source": [
    "## 5.1 Data Split\n",
    "- Training data period: 2009-01-01 to 2019-01-01\n",
    "- Trade data period: 2019-01-01 to 2020-12-31"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "W0qaVGjLtgbI",
    "outputId": "b24ebf9d-9477-4f5a-83cb-917abca11890"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "109530\n",
      "21930\n"
     ]
    }
   ],
   "source": [
    "train_data = data_split(processed_full, TRAIN_START_DATE, TRAIN_END_DATE)\n",
    "trade_data = data_split(processed_full, TEST_START_DATE, TEST_END_DATE)\n",
    "# Check the length of the two datasets\n",
    "print(len(train_data))\n",
    "print(len(trade_data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 386
    },
    "id": "p52zNCOhTtLR",
    "outputId": "da51cc43-921b-48d8-b82e-4f490bd61703"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-d8d443d8-6ef4-4c96-af1d-9fb728322643\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>...</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "      <th>PE</th>\n",
       "      <th>PB</th>\n",
       "      <th>Div_yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>3.067143</td>\n",
       "      <td>3.251429</td>\n",
       "      <td>3.041429</td>\n",
       "      <td>2.767330</td>\n",
       "      <td>746015200.0</td>\n",
       "      <td>0.217886</td>\n",
       "      <td>0.163846</td>\n",
       "      <td>0.103222</td>\n",
       "      <td>...</td>\n",
       "      <td>2.039779</td>\n",
       "      <td>1.818995</td>\n",
       "      <td>54.403846</td>\n",
       "      <td>8.972003</td>\n",
       "      <td>4.269115</td>\n",
       "      <td>0.437727</td>\n",
       "      <td>0.778495</td>\n",
       "      <td>0.636168</td>\n",
       "      <td>0.101527</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>AMGN</td>\n",
       "      <td>58.590000</td>\n",
       "      <td>59.080002</td>\n",
       "      <td>57.750000</td>\n",
       "      <td>44.523743</td>\n",
       "      <td>6547900.0</td>\n",
       "      <td>0.093973</td>\n",
       "      <td>0.072040</td>\n",
       "      <td>0.014094</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.351354</td>\n",
       "      <td>0.653355</td>\n",
       "      <td>0.869784</td>\n",
       "      <td>6.679531</td>\n",
       "      <td>143.624976</td>\n",
       "      <td>4.123353</td>\n",
       "      <td>0.004043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>AXP</td>\n",
       "      <td>18.570000</td>\n",
       "      <td>19.520000</td>\n",
       "      <td>18.400000</td>\n",
       "      <td>15.477424</td>\n",
       "      <td>10955700.0</td>\n",
       "      <td>0.093973</td>\n",
       "      <td>0.072040</td>\n",
       "      <td>0.014094</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.351354</td>\n",
       "      <td>0.653355</td>\n",
       "      <td>0.869784</td>\n",
       "      <td>6.679531</td>\n",
       "      <td>49.927173</td>\n",
       "      <td>1.433367</td>\n",
       "      <td>0.011630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>BA</td>\n",
       "      <td>42.799999</td>\n",
       "      <td>45.560001</td>\n",
       "      <td>42.779999</td>\n",
       "      <td>33.941097</td>\n",
       "      <td>7010200.0</td>\n",
       "      <td>0.047307</td>\n",
       "      <td>0.032525</td>\n",
       "      <td>0.026400</td>\n",
       "      <td>...</td>\n",
       "      <td>0.368463</td>\n",
       "      <td>0.148507</td>\n",
       "      <td>2.329670</td>\n",
       "      <td>6.815203</td>\n",
       "      <td>2.076967</td>\n",
       "      <td>1.009198</td>\n",
       "      <td>-109.722986</td>\n",
       "      <td>39.012755</td>\n",
       "      <td>-35.751050</td>\n",
       "      <td>0.012374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>CAT</td>\n",
       "      <td>44.910000</td>\n",
       "      <td>46.980000</td>\n",
       "      <td>44.709999</td>\n",
       "      <td>31.942245</td>\n",
       "      <td>7117200.0</td>\n",
       "      <td>0.124545</td>\n",
       "      <td>0.066662</td>\n",
       "      <td>0.040891</td>\n",
       "      <td>...</td>\n",
       "      <td>0.890488</td>\n",
       "      <td>0.163158</td>\n",
       "      <td>3.540791</td>\n",
       "      <td>2.460351</td>\n",
       "      <td>8.472455</td>\n",
       "      <td>0.893715</td>\n",
       "      <td>9.089489</td>\n",
       "      <td>-168.117081</td>\n",
       "      <td>3.083088</td>\n",
       "      <td>0.013149</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d8d443d8-6ef4-4c96-af1d-9fb728322643')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-d8d443d8-6ef4-4c96-af1d-9fb728322643 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-d8d443d8-6ef4-4c96-af1d-9fb728322643');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        date   tic       open       high        low      close       volume  \\\n",
       "0 2009-01-02  AAPL   3.067143   3.251429   3.041429   2.767330  746015200.0   \n",
       "0 2009-01-02  AMGN  58.590000  59.080002  57.750000  44.523743    6547900.0   \n",
       "0 2009-01-02   AXP  18.570000  19.520000  18.400000  15.477424   10955700.0   \n",
       "0 2009-01-02    BA  42.799999  45.560001  42.779999  33.941097    7010200.0   \n",
       "0 2009-01-02   CAT  44.910000  46.980000  44.709999  31.942245    7117200.0   \n",
       "\n",
       "        OPM       NPM       ROA  ...  quick_ratio  cash_ratio  inv_turnover  \\\n",
       "0  0.217886  0.163846  0.103222  ...     2.039779    1.818995     54.403846   \n",
       "0  0.093973  0.072040  0.014094  ...     0.000000    0.000000      0.000000   \n",
       "0  0.093973  0.072040  0.014094  ...     0.000000    0.000000      0.000000   \n",
       "0  0.047307  0.032525  0.026400  ...     0.368463    0.148507      2.329670   \n",
       "0  0.124545  0.066662  0.040891  ...     0.890488    0.163158      3.540791   \n",
       "\n",
       "   acc_rec_turnover  acc_pay_turnover  debt_ratio  debt_to_equity          PE  \\\n",
       "0          8.972003          4.269115    0.437727        0.778495    0.636168   \n",
       "0          0.351354          0.653355    0.869784        6.679531  143.624976   \n",
       "0          0.351354          0.653355    0.869784        6.679531   49.927173   \n",
       "0          6.815203          2.076967    1.009198     -109.722986   39.012755   \n",
       "0          2.460351          8.472455    0.893715        9.089489 -168.117081   \n",
       "\n",
       "          PB  Div_yield  \n",
       "0   0.101527   0.000000  \n",
       "0   4.123353   0.004043  \n",
       "0   1.433367   0.011630  \n",
       "0 -35.751050   0.012374  \n",
       "0   3.083088   0.013149  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 386
    },
    "id": "k9zU9YaTTvFq",
    "outputId": "9dd0d449-e994-4c80-b205-73098cce6480"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-732b9f63-d2a7-4bc5-bac1-d53a022f9f45\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>...</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "      <th>PE</th>\n",
       "      <th>PB</th>\n",
       "      <th>Div_yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>38.722500</td>\n",
       "      <td>39.712502</td>\n",
       "      <td>38.557499</td>\n",
       "      <td>38.168346</td>\n",
       "      <td>148158800.0</td>\n",
       "      <td>0.258891</td>\n",
       "      <td>0.227773</td>\n",
       "      <td>0.133360</td>\n",
       "      <td>...</td>\n",
       "      <td>1.134347</td>\n",
       "      <td>0.854114</td>\n",
       "      <td>23.571867</td>\n",
       "      <td>7.620024</td>\n",
       "      <td>3.781658</td>\n",
       "      <td>0.690466</td>\n",
       "      <td>2.230663</td>\n",
       "      <td>5.696768</td>\n",
       "      <td>1.661179</td>\n",
       "      <td>0.019126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>AMGN</td>\n",
       "      <td>192.520004</td>\n",
       "      <td>193.199997</td>\n",
       "      <td>188.949997</td>\n",
       "      <td>171.580246</td>\n",
       "      <td>3009100.0</td>\n",
       "      <td>0.093973</td>\n",
       "      <td>0.072040</td>\n",
       "      <td>0.014094</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.351354</td>\n",
       "      <td>0.653355</td>\n",
       "      <td>0.869784</td>\n",
       "      <td>6.679531</td>\n",
       "      <td>553.484664</td>\n",
       "      <td>15.890083</td>\n",
       "      <td>0.001049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>AXP</td>\n",
       "      <td>93.910004</td>\n",
       "      <td>96.269997</td>\n",
       "      <td>93.769997</td>\n",
       "      <td>90.748329</td>\n",
       "      <td>4175400.0</td>\n",
       "      <td>0.203479</td>\n",
       "      <td>0.160494</td>\n",
       "      <td>0.026811</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.231669</td>\n",
       "      <td>0.279424</td>\n",
       "      <td>0.887329</td>\n",
       "      <td>7.875371</td>\n",
       "      <td>50.137198</td>\n",
       "      <td>3.418685</td>\n",
       "      <td>0.004298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>BA</td>\n",
       "      <td>316.190002</td>\n",
       "      <td>323.950012</td>\n",
       "      <td>313.709991</td>\n",
       "      <td>314.645172</td>\n",
       "      <td>3292200.0</td>\n",
       "      <td>0.116496</td>\n",
       "      <td>0.102682</td>\n",
       "      <td>0.066409</td>\n",
       "      <td>...</td>\n",
       "      <td>0.262465</td>\n",
       "      <td>0.092436</td>\n",
       "      <td>0.933164</td>\n",
       "      <td>5.468453</td>\n",
       "      <td>4.151637</td>\n",
       "      <td>0.998070</td>\n",
       "      <td>517.142241</td>\n",
       "      <td>83.019834</td>\n",
       "      <td>1418.196409</td>\n",
       "      <td>0.006531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>CAT</td>\n",
       "      <td>124.029999</td>\n",
       "      <td>127.879997</td>\n",
       "      <td>123.000000</td>\n",
       "      <td>114.941383</td>\n",
       "      <td>4783200.0</td>\n",
       "      <td>0.186871</td>\n",
       "      <td>0.107064</td>\n",
       "      <td>0.056932</td>\n",
       "      <td>...</td>\n",
       "      <td>0.919490</td>\n",
       "      <td>0.266175</td>\n",
       "      <td>2.135008</td>\n",
       "      <td>2.339630</td>\n",
       "      <td>3.660183</td>\n",
       "      <td>0.803394</td>\n",
       "      <td>4.086316</td>\n",
       "      <td>34.936591</td>\n",
       "      <td>4.256800</td>\n",
       "      <td>0.007482</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-732b9f63-d2a7-4bc5-bac1-d53a022f9f45')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-732b9f63-d2a7-4bc5-bac1-d53a022f9f45 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-732b9f63-d2a7-4bc5-bac1-d53a022f9f45');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        date   tic        open        high         low       close  \\\n",
       "0 2019-01-01  AAPL   38.722500   39.712502   38.557499   38.168346   \n",
       "0 2019-01-01  AMGN  192.520004  193.199997  188.949997  171.580246   \n",
       "0 2019-01-01   AXP   93.910004   96.269997   93.769997   90.748329   \n",
       "0 2019-01-01    BA  316.190002  323.950012  313.709991  314.645172   \n",
       "0 2019-01-01   CAT  124.029999  127.879997  123.000000  114.941383   \n",
       "\n",
       "        volume       OPM       NPM       ROA  ...  quick_ratio  cash_ratio  \\\n",
       "0  148158800.0  0.258891  0.227773  0.133360  ...     1.134347    0.854114   \n",
       "0    3009100.0  0.093973  0.072040  0.014094  ...     0.000000    0.000000   \n",
       "0    4175400.0  0.203479  0.160494  0.026811  ...     0.000000    0.000000   \n",
       "0    3292200.0  0.116496  0.102682  0.066409  ...     0.262465    0.092436   \n",
       "0    4783200.0  0.186871  0.107064  0.056932  ...     0.919490    0.266175   \n",
       "\n",
       "   inv_turnover  acc_rec_turnover  acc_pay_turnover  debt_ratio  \\\n",
       "0     23.571867          7.620024          3.781658    0.690466   \n",
       "0      0.000000          0.351354          0.653355    0.869784   \n",
       "0      0.000000          0.231669          0.279424    0.887329   \n",
       "0      0.933164          5.468453          4.151637    0.998070   \n",
       "0      2.135008          2.339630          3.660183    0.803394   \n",
       "\n",
       "   debt_to_equity          PE           PB  Div_yield  \n",
       "0        2.230663    5.696768     1.661179   0.019126  \n",
       "0        6.679531  553.484664    15.890083   0.001049  \n",
       "0        7.875371   50.137198     3.418685   0.004298  \n",
       "0      517.142241   83.019834  1418.196409   0.006531  \n",
       "0        4.086316   34.936591     4.256800   0.007482  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qqGG78pLyCX7"
   },
   "source": [
    "## 5.2 Set up the training environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "id": "LPD0wZLO-Pse"
   },
   "outputs": [],
   "source": [
    "import gym\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from gym import spaces\n",
    "from gym.utils import seeding\n",
    "from stable_baselines3.common.vec_env import DummyVecEnv\n",
    "\n",
    "matplotlib.use(\"Agg\")\n",
    "\n",
    "# from stable_baselines3.common import logger\n",
    "\n",
    "\n",
    "class StockTradingEnv(gym.Env):\n",
    "    \"\"\"A stock trading environment for OpenAI gym\"\"\"\n",
    "\n",
    "    metadata = {\"render.modes\": [\"human\"]}\n",
    "\n",
    "    def __init__(\n",
    "        self,\n",
    "        df,\n",
    "        stock_dim,\n",
    "        hmax,\n",
    "        initial_amount,\n",
    "        buy_cost_pct,\n",
    "        sell_cost_pct,\n",
    "        reward_scaling,\n",
    "        state_space,\n",
    "        action_space,\n",
    "        tech_indicator_list,\n",
    "        turbulence_threshold=None,\n",
    "        risk_indicator_col=\"turbulence\",\n",
    "        make_plots=False,\n",
    "        print_verbosity=10,\n",
    "        day=0,\n",
    "        initial=True,\n",
    "        previous_state=[],\n",
    "        model_name=\"\",\n",
    "        mode=\"\",\n",
    "        iteration=\"\",\n",
    "    ):\n",
    "        self.day = day\n",
    "        self.df = df\n",
    "        self.stock_dim = stock_dim\n",
    "        self.hmax = hmax\n",
    "        self.initial_amount = initial_amount\n",
    "        self.buy_cost_pct = buy_cost_pct\n",
    "        self.sell_cost_pct = sell_cost_pct\n",
    "        self.reward_scaling = reward_scaling\n",
    "        self.state_space = state_space\n",
    "        self.action_space = action_space\n",
    "        self.tech_indicator_list = tech_indicator_list\n",
    "        self.action_space = spaces.Box(low=-1, high=1, shape=(self.action_space,))\n",
    "        self.observation_space = spaces.Box(\n",
    "            low=-np.inf, high=np.inf, shape=(self.state_space,)\n",
    "        )\n",
    "        self.data = self.df.loc[self.day, :]\n",
    "        self.terminal = False\n",
    "        self.make_plots = make_plots\n",
    "        self.print_verbosity = print_verbosity\n",
    "        self.turbulence_threshold = turbulence_threshold\n",
    "        self.risk_indicator_col = risk_indicator_col\n",
    "        self.initial = initial\n",
    "        self.previous_state = previous_state\n",
    "        self.model_name = model_name\n",
    "        self.mode = mode\n",
    "        self.iteration = iteration\n",
    "        # initalize state\n",
    "        self.state = self._initiate_state()\n",
    "\n",
    "        # initialize reward\n",
    "        self.reward = 0\n",
    "        self.turbulence = 0\n",
    "        self.cost = 0\n",
    "        self.trades = 0\n",
    "        self.episode = 0\n",
    "        # memorize all the total balance change\n",
    "        self.asset_memory = [self.initial_amount]\n",
    "        self.rewards_memory = []\n",
    "        self.actions_memory = []\n",
    "        self.date_memory = [self._get_date()]\n",
    "        # self.reset()\n",
    "        self._seed()\n",
    "\n",
    "    def _sell_stock(self, index, action):\n",
    "        def _do_sell_normal():\n",
    "            if self.state[index + 1] > 0:\n",
    "                # Sell only if the price is > 0 (no missing data in this particular date)\n",
    "                # perform sell action based on the sign of the action\n",
    "                if self.state[index + self.stock_dim + 1] > 0:\n",
    "                    # Sell only if current asset is > 0\n",
    "                    sell_num_shares = min(\n",
    "                        abs(action), self.state[index + self.stock_dim + 1]\n",
    "                    )\n",
    "                    sell_amount = (\n",
    "                        self.state[index + 1]\n",
    "                        * sell_num_shares\n",
    "                        * (1 - self.sell_cost_pct)\n",
    "                    )\n",
    "                    # update balance\n",
    "                    self.state[0] += sell_amount\n",
    "\n",
    "                    self.state[index + self.stock_dim + 1] -= sell_num_shares\n",
    "                    self.cost += (\n",
    "                        self.state[index + 1] * sell_num_shares * self.sell_cost_pct\n",
    "                    )\n",
    "                    self.trades += 1\n",
    "                else:\n",
    "                    sell_num_shares = 0\n",
    "            else:\n",
    "                sell_num_shares = 0\n",
    "\n",
    "            return sell_num_shares\n",
    "\n",
    "        # perform sell action based on the sign of the action\n",
    "        if self.turbulence_threshold is not None:\n",
    "            if self.turbulence >= self.turbulence_threshold:\n",
    "                if self.state[index + 1] > 0:\n",
    "                    # Sell only if the price is > 0 (no missing data in this particular date)\n",
    "                    # if turbulence goes over threshold, just clear out all positions\n",
    "                    if self.state[index + self.stock_dim + 1] > 0:\n",
    "                        # Sell only if current asset is > 0\n",
    "                        sell_num_shares = self.state[index + self.stock_dim + 1]\n",
    "                        sell_amount = (\n",
    "                            self.state[index + 1]\n",
    "                            * sell_num_shares\n",
    "                            * (1 - self.sell_cost_pct)\n",
    "                        )\n",
    "                        # update balance\n",
    "                        self.state[0] += sell_amount\n",
    "                        self.state[index + self.stock_dim + 1] = 0\n",
    "                        self.cost += (\n",
    "                            self.state[index + 1] * sell_num_shares * self.sell_cost_pct\n",
    "                        )\n",
    "                        self.trades += 1\n",
    "                    else:\n",
    "                        sell_num_shares = 0\n",
    "                else:\n",
    "                    sell_num_shares = 0\n",
    "            else:\n",
    "                sell_num_shares = _do_sell_normal()\n",
    "        else:\n",
    "            sell_num_shares = _do_sell_normal()\n",
    "\n",
    "        return sell_num_shares\n",
    "\n",
    "    def _buy_stock(self, index, action):\n",
    "        def _do_buy():\n",
    "            if self.state[index + 1] > 0:\n",
    "                # Buy only if the price is > 0 (no missing data in this particular date)\n",
    "                available_amount = self.state[0] // self.state[index + 1]\n",
    "                # print('available_amount:{}'.format(available_amount))\n",
    "\n",
    "                # update balance\n",
    "                buy_num_shares = min(available_amount, action)\n",
    "                buy_amount = (\n",
    "                    self.state[index + 1] * buy_num_shares * (1 + self.buy_cost_pct)\n",
    "                )\n",
    "                self.state[0] -= buy_amount\n",
    "\n",
    "                self.state[index + self.stock_dim + 1] += buy_num_shares\n",
    "\n",
    "                self.cost += self.state[index + 1] * buy_num_shares * self.buy_cost_pct\n",
    "                self.trades += 1\n",
    "            else:\n",
    "                buy_num_shares = 0\n",
    "\n",
    "            return buy_num_shares\n",
    "\n",
    "        # perform buy action based on the sign of the action\n",
    "        if self.turbulence_threshold is None:\n",
    "            buy_num_shares = _do_buy()\n",
    "        else:\n",
    "            if self.turbulence < self.turbulence_threshold:\n",
    "                buy_num_shares = _do_buy()\n",
    "            else:\n",
    "                buy_num_shares = 0\n",
    "                pass\n",
    "\n",
    "        return buy_num_shares\n",
    "\n",
    "    def _make_plot(self):\n",
    "        plt.plot(self.asset_memory, \"r\")\n",
    "        plt.savefig(\"results/account_value_trade_{}.png\".format(self.episode))\n",
    "        plt.close()\n",
    "\n",
    "    def step(self, actions):\n",
    "        self.terminal = self.day >= len(self.df.index.unique()) - 1\n",
    "        if self.terminal:\n",
    "            # print(f\"Episode: {self.episode}\")\n",
    "            if self.make_plots:\n",
    "                self._make_plot()\n",
    "            end_total_asset = self.state[0] + sum(\n",
    "                np.array(self.state[1 : (self.stock_dim + 1)])\n",
    "                * np.array(self.state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)])\n",
    "            )\n",
    "            df_total_value = pd.DataFrame(self.asset_memory)\n",
    "            tot_reward = (\n",
    "                self.state[0]\n",
    "                + sum(\n",
    "                    np.array(self.state[1 : (self.stock_dim + 1)])\n",
    "                    * np.array(\n",
    "                        self.state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)]\n",
    "                    )\n",
    "                )\n",
    "                - self.initial_amount\n",
    "            )\n",
    "            df_total_value.columns = [\"account_value\"]\n",
    "            df_total_value[\"date\"] = self.date_memory\n",
    "            df_total_value[\"daily_return\"] = df_total_value[\"account_value\"].pct_change(\n",
    "                1\n",
    "            )\n",
    "            if df_total_value[\"daily_return\"].std() != 0:\n",
    "                sharpe = (\n",
    "                    (252 ** 0.5)\n",
    "                    * df_total_value[\"daily_return\"].mean()\n",
    "                    / df_total_value[\"daily_return\"].std()\n",
    "                )\n",
    "            df_rewards = pd.DataFrame(self.rewards_memory)\n",
    "            df_rewards.columns = [\"account_rewards\"]\n",
    "            df_rewards[\"date\"] = self.date_memory[:-1]\n",
    "            if self.episode % self.print_verbosity == 0:\n",
    "                print(f\"day: {self.day}, episode: {self.episode}\")\n",
    "                print(f\"begin_total_asset: {self.asset_memory[0]:0.2f}\")\n",
    "                print(f\"end_total_asset: {end_total_asset:0.2f}\")\n",
    "                print(f\"total_reward: {tot_reward:0.2f}\")\n",
    "                print(f\"total_cost: {self.cost:0.2f}\")\n",
    "                print(f\"total_trades: {self.trades}\")\n",
    "                if df_total_value[\"daily_return\"].std() != 0:\n",
    "                    print(f\"Sharpe: {sharpe:0.3f}\")\n",
    "                print(\"=================================\")\n",
    "\n",
    "            if (self.model_name != \"\") and (self.mode != \"\"):\n",
    "                df_actions = self.save_action_memory()\n",
    "                df_actions.to_csv(\n",
    "                    \"results/actions_{}_{}_{}.csv\".format(\n",
    "                        self.mode, self.model_name, self.iteration\n",
    "                    )\n",
    "                )\n",
    "                df_total_value.to_csv(\n",
    "                    \"results/account_value_{}_{}_{}.csv\".format(\n",
    "                        self.mode, self.model_name, self.iteration\n",
    "                    ),\n",
    "                    index=False,\n",
    "                )\n",
    "                df_rewards.to_csv(\n",
    "                    \"results/account_rewards_{}_{}_{}.csv\".format(\n",
    "                        self.mode, self.model_name, self.iteration\n",
    "                    ),\n",
    "                    index=False,\n",
    "                )\n",
    "                plt.plot(self.asset_memory, \"r\")\n",
    "                plt.savefig(\n",
    "                    \"results/account_value_{}_{}_{}.png\".format(\n",
    "                        self.mode, self.model_name, self.iteration\n",
    "                    ),\n",
    "                    index=False,\n",
    "                )\n",
    "                plt.close()\n",
    "\n",
    "            # Add outputs to logger interface\n",
    "            # logger.record(\"environment/portfolio_value\", end_total_asset)\n",
    "            # logger.record(\"environment/total_reward\", tot_reward)\n",
    "            # logger.record(\"environment/total_reward_pct\", (tot_reward / (end_total_asset - tot_reward)) * 100)\n",
    "            # logger.record(\"environment/total_cost\", self.cost)\n",
    "            # logger.record(\"environment/total_trades\", self.trades)\n",
    "\n",
    "            return self.state, self.reward, self.terminal, {}\n",
    "\n",
    "        else:\n",
    "\n",
    "            actions = actions * self.hmax  # actions initially is scaled between 0 to 1\n",
    "            actions = actions.astype(\n",
    "                int\n",
    "            )  # convert into integer because we can't by fraction of shares\n",
    "            if self.turbulence_threshold is not None:\n",
    "                if self.turbulence >= self.turbulence_threshold:\n",
    "                    actions = np.array([-self.hmax] * self.stock_dim)\n",
    "            begin_total_asset = self.state[0] + sum(\n",
    "                np.array(self.state[1 : (self.stock_dim + 1)])\n",
    "                * np.array(self.state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)])\n",
    "            )\n",
    "            # print(\"begin_total_asset:{}\".format(begin_total_asset))\n",
    "\n",
    "            argsort_actions = np.argsort(actions)\n",
    "\n",
    "            sell_index = argsort_actions[: np.where(actions < 0)[0].shape[0]]\n",
    "            buy_index = argsort_actions[::-1][: np.where(actions > 0)[0].shape[0]]\n",
    "\n",
    "            for index in sell_index:\n",
    "                # print(f\"Num shares before: {self.state[index+self.stock_dim+1]}\")\n",
    "                # print(f'take sell action before : {actions[index]}')\n",
    "                actions[index] = self._sell_stock(index, actions[index]) * (-1)\n",
    "                # print(f'take sell action after : {actions[index]}')\n",
    "                # print(f\"Num shares after: {self.state[index+self.stock_dim+1]}\")\n",
    "\n",
    "            for index in buy_index:\n",
    "                # print('take buy action: {}'.format(actions[index]))\n",
    "                actions[index] = self._buy_stock(index, actions[index])\n",
    "\n",
    "            self.actions_memory.append(actions)\n",
    "\n",
    "            # state: s -> s+1\n",
    "            self.day += 1\n",
    "            self.data = self.df.loc[self.day, :]\n",
    "            if self.turbulence_threshold is not None:\n",
    "                if len(self.df.tic.unique()) == 1:\n",
    "                    self.turbulence = self.data[self.risk_indicator_col]\n",
    "                elif len(self.df.tic.unique()) > 1:\n",
    "                    self.turbulence = self.data[self.risk_indicator_col].values[0]\n",
    "            self.state = self._update_state()\n",
    "\n",
    "            end_total_asset = self.state[0] + sum(\n",
    "                np.array(self.state[1 : (self.stock_dim + 1)])\n",
    "                * np.array(self.state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)])\n",
    "            )\n",
    "            self.asset_memory.append(end_total_asset)\n",
    "            self.date_memory.append(self._get_date())\n",
    "            self.reward = end_total_asset - begin_total_asset\n",
    "            self.rewards_memory.append(self.reward)\n",
    "            self.reward = self.reward * self.reward_scaling\n",
    "\n",
    "        return self.state, self.reward, self.terminal, {}\n",
    "\n",
    "    def reset(self):\n",
    "        # initiate state\n",
    "        self.state = self._initiate_state()\n",
    "\n",
    "        if self.initial:\n",
    "            self.asset_memory = [self.initial_amount]\n",
    "        else:\n",
    "            previous_total_asset = self.previous_state[0] + sum(\n",
    "                np.array(self.state[1 : (self.stock_dim + 1)])\n",
    "                * np.array(\n",
    "                    self.previous_state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)]\n",
    "                )\n",
    "            )\n",
    "            self.asset_memory = [previous_total_asset]\n",
    "\n",
    "        self.day = 0\n",
    "        self.data = self.df.loc[self.day, :]\n",
    "        self.turbulence = 0\n",
    "        self.cost = 0\n",
    "        self.trades = 0\n",
    "        self.terminal = False\n",
    "        # self.iteration=self.iteration\n",
    "        self.rewards_memory = []\n",
    "        self.actions_memory = []\n",
    "        self.date_memory = [self._get_date()]\n",
    "\n",
    "        self.episode += 1\n",
    "\n",
    "        return self.state\n",
    "\n",
    "    def render(self, mode=\"human\", close=False):\n",
    "        return self.state\n",
    "\n",
    "    def _initiate_state(self):\n",
    "        if self.initial:\n",
    "            # For Initial State\n",
    "            if len(self.df.tic.unique()) > 1:\n",
    "                # for multiple stock\n",
    "                state = (\n",
    "                    [self.initial_amount]\n",
    "                    + self.data.close.values.tolist()\n",
    "                    + [0] * self.stock_dim\n",
    "                    + sum(\n",
    "                        [\n",
    "                            self.data[tech].values.tolist()\n",
    "                            for tech in self.tech_indicator_list\n",
    "                        ],\n",
    "                        [],\n",
    "                    )\n",
    "                )\n",
    "            else:\n",
    "                # for single stock\n",
    "                state = (\n",
    "                    [self.initial_amount]\n",
    "                    + [self.data.close]\n",
    "                    + [0] * self.stock_dim\n",
    "                    + sum([[self.data[tech]] for tech in self.tech_indicator_list], [])\n",
    "                )\n",
    "        else:\n",
    "            # Using Previous State\n",
    "            if len(self.df.tic.unique()) > 1:\n",
    "                # for multiple stock\n",
    "                state = (\n",
    "                    [self.previous_state[0]]\n",
    "                    + self.data.close.values.tolist()\n",
    "                    + self.previous_state[\n",
    "                        (self.stock_dim + 1) : (self.stock_dim * 2 + 1)\n",
    "                    ]\n",
    "                    + sum(\n",
    "                        [\n",
    "                            self.data[tech].values.tolist()\n",
    "                            for tech in self.tech_indicator_list\n",
    "                        ],\n",
    "                        [],\n",
    "                    )\n",
    "                )\n",
    "            else:\n",
    "                # for single stock\n",
    "                state = (\n",
    "                    [self.previous_state[0]]\n",
    "                    + [self.data.close]\n",
    "                    + self.previous_state[\n",
    "                        (self.stock_dim + 1) : (self.stock_dim * 2 + 1)\n",
    "                    ]\n",
    "                    + sum([[self.data[tech]] for tech in self.tech_indicator_list], [])\n",
    "                )\n",
    "        return state\n",
    "\n",
    "    def _update_state(self):\n",
    "        if len(self.df.tic.unique()) > 1:\n",
    "            # for multiple stock\n",
    "            state = (\n",
    "                [self.state[0]]\n",
    "                + self.data.close.values.tolist()\n",
    "                + list(self.state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)])\n",
    "                + sum(\n",
    "                    [\n",
    "                        self.data[tech].values.tolist()\n",
    "                        for tech in self.tech_indicator_list\n",
    "                    ],\n",
    "                    [],\n",
    "                )\n",
    "            )\n",
    "\n",
    "        else:\n",
    "            # for single stock\n",
    "            state = (\n",
    "                [self.state[0]]\n",
    "                + [self.data.close]\n",
    "                + list(self.state[(self.stock_dim + 1) : (self.stock_dim * 2 + 1)])\n",
    "                + sum([[self.data[tech]] for tech in self.tech_indicator_list], [])\n",
    "            )\n",
    "        return state\n",
    "\n",
    "    def _get_date(self):\n",
    "        if len(self.df.tic.unique()) > 1:\n",
    "            date = self.data.date.unique()[0]\n",
    "        else:\n",
    "            date = self.data.date\n",
    "        return date\n",
    "\n",
    "    def save_asset_memory(self):\n",
    "        date_list = self.date_memory\n",
    "        asset_list = self.asset_memory\n",
    "        # print(len(date_list))\n",
    "        # print(len(asset_list))\n",
    "        df_account_value = pd.DataFrame(\n",
    "            {\"date\": date_list, \"account_value\": asset_list}\n",
    "        )\n",
    "        return df_account_value\n",
    "\n",
    "    def save_action_memory(self):\n",
    "        if len(self.df.tic.unique()) > 1:\n",
    "            # date and close price length must match actions length\n",
    "            date_list = self.date_memory[:-1]\n",
    "            df_date = pd.DataFrame(date_list)\n",
    "            df_date.columns = [\"date\"]\n",
    "\n",
    "            action_list = self.actions_memory\n",
    "            df_actions = pd.DataFrame(action_list)\n",
    "            df_actions.columns = self.data.tic.values\n",
    "            df_actions.index = df_date.date\n",
    "            # df_actions = pd.DataFrame({'date':date_list,'actions':action_list})\n",
    "        else:\n",
    "            date_list = self.date_memory[:-1]\n",
    "            action_list = self.actions_memory\n",
    "            df_actions = pd.DataFrame({\"date\": date_list, \"actions\": action_list})\n",
    "        return df_actions\n",
    "\n",
    "    def _seed(self, seed=None):\n",
    "        self.np_random, seed = seeding.np_random(seed)\n",
    "        return [seed]\n",
    "\n",
    "    def get_sb_env(self):\n",
    "        e = DummyVecEnv([lambda: self])\n",
    "        obs = e.reset()\n",
    "        return e, obs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Q2zqII8rMIqn",
    "outputId": "0f9a64c0-72b1-448e-f1d9-8e59bfc13251"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stock Dimension: 30, State Space: 511\n"
     ]
    }
   ],
   "source": [
    "ratio_list = ['OPM', 'NPM','ROA', 'ROE', 'cur_ratio', 'quick_ratio', 'cash_ratio', 'inv_turnover','acc_rec_turnover', 'acc_pay_turnover', 'debt_ratio', 'debt_to_equity',\n",
    "       'PE', 'PB', 'Div_yield']\n",
    "\n",
    "stock_dimension = len(train_data.tic.unique())\n",
    "state_space = 1 + 2*stock_dimension + len(ratio_list)*stock_dimension\n",
    "print(f\"Stock Dimension: {stock_dimension}, State Space: {state_space}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "id": "AWyp84Ltto19"
   },
   "outputs": [],
   "source": [
    "# Parameters for the environment\n",
    "num_stock_shares = [0] * stock_dimension\n",
    "env_kwargs = {\n",
    "    \"hmax\": 100, \n",
    "    \"initial_amount\": 1000000, \n",
    "    \"buy_cost_pct\": 0.001,\n",
    "    \"sell_cost_pct\": 0.001,\n",
    "    \"state_space\": state_space, \n",
    "    \"stock_dim\": stock_dimension, \n",
    "    \"tech_indicator_list\": ratio_list, \n",
    "    \"action_space\": stock_dimension, \n",
    "    \"reward_scaling\": 1e-4\n",
    "    \n",
    "}\n",
    "\n",
    "#Establish the training environment using StockTradingEnv() class\n",
    "e_train_gym = StockTradingEnv(df = train_data, **env_kwargs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "64EoqOrQjiVf"
   },
   "source": [
    "## Environment for Training\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "xwSvvPjutpqS",
    "outputId": "02395134-f4bd-433e-ac0b-4799f25bec70"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'stable_baselines3.common.vec_env.dummy_vec_env.DummyVecEnv'>\n"
     ]
    }
   ],
   "source": [
    "env_train, _ = e_train_gym.get_sb_env()\n",
    "print(type(env_train))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HMNR5nHjh1iz"
   },
   "source": [
    "<a id='5'></a>\n",
    "# Part 6: Train DRL Agents\n",
    "* The DRL algorithms are from **Stable Baselines 3**. Users are also encouraged to try **ElegantRL** and **Ray RLlib**.\n",
    "* FinRL library includes fine-tuned standard DRL algorithms, such as DQN, DDPG,\n",
    "Multi-Agent DDPG, PPO, SAC, A2C and TD3. We also allow users to\n",
    "design their own DRL algorithms by adapting these DRL algorithms."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "id": "364PsqckttcQ"
   },
   "outputs": [],
   "source": [
    "# Set up the agent using DRLAgent() class using the environment created in the previous part\n",
    "agent = DRLAgent(env = env_train)\n",
    "\n",
    "if_using_a2c = False\n",
    "if_using_ddpg = False\n",
    "if_using_ppo = False\n",
    "if_using_td3 = False\n",
    "if_using_sac = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YDmqOyF9h1iz"
   },
   "source": [
    "### Agent Training: 5 algorithms (A2C, DDPG, PPO, TD3, SAC)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_gDkU-j-fCmZ"
   },
   "source": [
    "### Model 1: PPO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "y5D5PFUhMzSV",
    "outputId": "aaff4227-3705-4699-a884-11799d07c372"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'n_steps': 2048, 'ent_coef': 0.01, 'learning_rate': 0.00025, 'batch_size': 128}\n",
      "Using cpu device\n"
     ]
    }
   ],
   "source": [
    "agent = DRLAgent(env = env_train)\n",
    "PPO_PARAMS = {\n",
    "    \"n_steps\": 2048,\n",
    "    \"ent_coef\": 0.01,\n",
    "    \"learning_rate\": 0.00025,\n",
    "    \"batch_size\": 128,\n",
    "}\n",
    "model_ppo = agent.get_model(\"ppo\",model_kwargs = PPO_PARAMS)\n",
    "\n",
    "if if_using_ppo:\n",
    "  # set up logger\n",
    "  tmp_path = RESULTS_DIR + '/ppo'\n",
    "  new_logger_ppo = configure(tmp_path, [\"stdout\", \"csv\", \"tensorboard\"])\n",
    "  # Set new logger\n",
    "  model_ppo.set_logger(new_logger_ppo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "id": "Gt8eIQKYM4G3"
   },
   "outputs": [],
   "source": [
    "trained_ppo = agent.train_model(model=model_ppo, \n",
    "                             tb_log_name='ppo',\n",
    "                             total_timesteps=50000) if if_using_ppo else None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MRiOtrywfAo1"
   },
   "source": [
    "### Model 2: DDPG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "M2YadjfnLwgt",
    "outputId": "5a0a7b35-d769-4a36-aae7-d2bd052434bd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'batch_size': 128, 'buffer_size': 50000, 'learning_rate': 0.001}\n",
      "Using cpu device\n"
     ]
    }
   ],
   "source": [
    "agent = DRLAgent(env = env_train)\n",
    "model_ddpg = agent.get_model(\"ddpg\")\n",
    "\n",
    "if if_using_ddpg:\n",
    "  # set up logger\n",
    "  tmp_path = RESULTS_DIR + '/ddpg'\n",
    "  new_logger_ddpg = configure(tmp_path, [\"stdout\", \"csv\", \"tensorboard\"])\n",
    "  # Set new logger\n",
    "  model_ddpg.set_logger(new_logger_ddpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true,
    "id": "tCDa78rqfO_a"
   },
   "outputs": [],
   "source": [
    "trained_ddpg = agent.train_model(model=model_ddpg, \n",
    "                             tb_log_name='ddpg',\n",
    "                             total_timesteps=50000) if if_using_ddpg else None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uijiWgkuh1jB"
   },
   "source": [
    "### Model 3: A2C\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "GUCnkn-HIbmj",
    "outputId": "2cfcb57e-44fc-4802-bc3b-ae9d262892f4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'n_steps': 5, 'ent_coef': 0.01, 'learning_rate': 0.0007}\n",
      "Using cpu device\n"
     ]
    }
   ],
   "source": [
    "agent = DRLAgent(env = env_train)\n",
    "model_a2c = agent.get_model(\"a2c\")\n",
    "\n",
    "if if_using_a2c:\n",
    "  # set up logger\n",
    "  tmp_path = RESULTS_DIR + '/a2c'\n",
    "  new_logger_a2c = configure(tmp_path, [\"stdout\", \"csv\", \"tensorboard\"])\n",
    "  # Set new logger\n",
    "  model_a2c.set_logger(new_logger_a2c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "id": "0GVpkWGqH4-D"
   },
   "outputs": [],
   "source": [
    "trained_a2c = agent.train_model(model=model_a2c, \n",
    "                             tb_log_name='a2c',\n",
    "                             total_timesteps=50000) if if_using_a2c else None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3Zpv4S0-fDBv"
   },
   "source": [
    "### Model 4: TD3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "JSAHhV4Xc-bh",
    "outputId": "728c0ece-df73-4665-c56b-e27f32721a26"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'batch_size': 100, 'buffer_size': 1000000, 'learning_rate': 0.001}\n",
      "Using cpu device\n"
     ]
    }
   ],
   "source": [
    "agent = DRLAgent(env = env_train)\n",
    "TD3_PARAMS = {\"batch_size\": 100, \n",
    "              \"buffer_size\": 1000000, \n",
    "              \"learning_rate\": 0.001}\n",
    "\n",
    "model_td3 = agent.get_model(\"td3\",model_kwargs = TD3_PARAMS)\n",
    "\n",
    "if if_using_td3:\n",
    "  # set up logger\n",
    "  tmp_path = RESULTS_DIR + '/td3'\n",
    "  new_logger_td3 = configure(tmp_path, [\"stdout\", \"csv\", \"tensorboard\"])\n",
    "  # Set new logger\n",
    "  model_td3.set_logger(new_logger_td3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "id": "OSRxNYAxdKpU"
   },
   "outputs": [],
   "source": [
    "trained_td3 = agent.train_model(model=model_td3, \n",
    "                             tb_log_name='td3',\n",
    "                             total_timesteps=30000) if if_using_td3 else None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Dr49PotrfG01"
   },
   "source": [
    "### Model 5: SAC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "xwOhVjqRkCdM",
    "outputId": "bbce7e94-dd48-48d5-86db-fbfae74f0175"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'batch_size': 128, 'buffer_size': 1000000, 'learning_rate': 0.0001, 'learning_starts': 100, 'ent_coef': 'auto_0.1'}\n",
      "Using cpu device\n",
      "Logging to results/sac\n"
     ]
    }
   ],
   "source": [
    "agent = DRLAgent(env = env_train)\n",
    "SAC_PARAMS = {\n",
    "    \"batch_size\": 128,\n",
    "    \"buffer_size\": 1000000,\n",
    "    \"learning_rate\": 0.0001,\n",
    "    \"learning_starts\": 100,\n",
    "    \"ent_coef\": \"auto_0.1\",\n",
    "}\n",
    "\n",
    "model_sac = agent.get_model(\"sac\",model_kwargs = SAC_PARAMS)\n",
    "\n",
    "if if_using_sac:\n",
    "  # set up logger\n",
    "  tmp_path = RESULTS_DIR + '/sac'\n",
    "  new_logger_sac = configure(tmp_path, [\"stdout\", \"csv\", \"tensorboard\"])\n",
    "  # Set new logger\n",
    "  model_sac.set_logger(new_logger_sac)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "K8RSdKCckJyH",
    "outputId": "3f71cc37-13e5-46e8-a1cf-3145027001db"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------\n",
      "| time/              |               |\n",
      "|    episodes        | 4             |\n",
      "|    fps             | 18            |\n",
      "|    time_elapsed    | 805           |\n",
      "|    total_timesteps | 14604         |\n",
      "| train/             |               |\n",
      "|    actor_loss      | 579           |\n",
      "|    critic_loss     | 40.6          |\n",
      "|    ent_coef        | 0.134         |\n",
      "|    ent_coef_loss   | -97.9         |\n",
      "|    learning_rate   | 0.0001        |\n",
      "|    n_updates       | 14503         |\n",
      "|    reward          | -0.0051038307 |\n",
      "--------------------------------------\n"
     ]
    }
   ],
   "source": [
    "trained_sac = agent.train_model(model=model_sac, \n",
    "                             tb_log_name='sac',\n",
    "                             total_timesteps=40000) if if_using_sac else None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f2wZgkQXh1jE"
   },
   "source": [
    "## Trading\n",
    "Assume that we have $1,000,000 initial capital at TEST_START_DATE. We use the DDPG model to trade Dow jones 30 stocks."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "U5mmgQF_h1jQ"
   },
   "source": [
    "### Trade\n",
    "\n",
    "DRL model needs to update periodically in order to take full advantage of the data, ideally we need to retrain our model yearly, quarterly, or monthly. We also need to tune the parameters along the way, in this notebook I only use the in-sample data from 2009-01 to 2018-12 to tune the parameters once, so there is some alpha decay here as the length of trade date extends. \n",
    "\n",
    "Numerous hyperparameters – e.g. the learning rate, the total number of samples to train on – influence the learning process and are usually determined by testing some variations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cIqoV0GSI52v"
   },
   "outputs": [],
   "source": [
    "trade_data = data_split(processed_full, TEST_START_DATE, TEST_END_DATE)\n",
    "e_trade_gym = StockTradingEnv(df = trade_data, **env_kwargs)\n",
    "# env_trade, obs_trade = e_trade_gym.get_sb_env()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "W_XNgGsBMeVw"
   },
   "outputs": [],
   "source": [
    "trade_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "eLOnL5eYh1jR"
   },
   "outputs": [],
   "source": [
    "df_account_value_ppo, df_actions_ppo = DRLAgent.DRL_prediction(\n",
    "    model=trained_ppo, \n",
    "    environment = e_trade_gym) if if_using_ppo else None\n",
    "\n",
    "df_account_value_ddpg, df_actions_ddpg = DRLAgent.DRL_prediction(\n",
    "    model=trained_ddpg, \n",
    "    environment = e_trade_gym) if if_using_ddpg else None\n",
    "\n",
    "df_account_value_a2c, df_actions_a2c = DRLAgent.DRL_prediction(\n",
    "    model=trained_a2c, \n",
    "    environment = e_trade_gym) if if_using_a2c else None\n",
    "\n",
    "df_account_value_td3, df_actions_td3 = DRLAgent.DRL_prediction(\n",
    "    model=trained_td3, \n",
    "    environment = e_trade_gym) if if_using_td3 else None\n",
    "\n",
    "df_account_value_sac, df_actions_sac = DRLAgent.DRL_prediction(\n",
    "    model=trained_sac, \n",
    "    environment = e_trade_gym) if if_using_sac else None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ERxw3KqLkcP4"
   },
   "outputs": [],
   "source": [
    "# df_account_value_ppo.shape\n",
    "# df_account_value_ddpg.shape\n",
    "# df_account_value_a2c.shape\n",
    "# df_account_value_td3.shape\n",
    "# df_account_value_sac.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2yRkNguY5yvp"
   },
   "outputs": [],
   "source": [
    "# df_account_value_ppo.tail()\n",
    "# df_account_value_ddpg.tail()\n",
    "# df_account_value_a2c.tail()\n",
    "# df_account_value_td3.tail()\n",
    "# df_account_value_sac.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "nFlK5hNbWVFk"
   },
   "outputs": [],
   "source": [
    "# df_actions_ppo.head()\n",
    "# df_actions_ddpg.head()\n",
    "# df_actions_a2c.head()\n",
    "# df_actions_td3.head()\n",
    "# df_actions_sac.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "W6vvNSC6h1jZ"
   },
   "source": [
    "<a id='6'></a>\n",
    "# Part 7: Backtest Our Strategy\n",
    "Backtesting plays a key role in evaluating the performance of a trading strategy. Automated backtesting tool is preferred because it reduces the human error. We usually use the Quantopian pyfolio package to backtest our trading strategies. It is easy to use and consists of various individual plots that provide a comprehensive image of the performance of a trading strategy."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Lr2zX7ZxNyFQ"
   },
   "source": [
    "<a id='6.1'></a>\n",
    "## 7.1 BackTestStats\n",
    "pass in df_account_value, this information is stored in env class\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Nzkr9yv-AdV_"
   },
   "outputs": [],
   "source": [
    "print(\"==============Get Backtest Results===========\")\n",
    "now = datetime.datetime.now().strftime('%Y%m%d-%Hh%M')\n",
    "\n",
    "if if_using_ppo:\n",
    "  print(\"\\n ppo:\")\n",
    "  perf_stats_all_ppo = backtest_stats(account_value=df_account_value_ppo)\n",
    "  perf_stats_all_ppo = pd.DataFrame(perf_stats_all_ppo)\n",
    "  perf_stats_all_ppo.to_csv(\"./\"+config.RESULTS_DIR+\"/perf_stats_all_ppo_\"+now+'.csv')\n",
    "\n",
    "if if_using_ddpg:\n",
    "  print(\"\\n ddpg:\")\n",
    "  perf_stats_all_ddpg = backtest_stats(account_value=df_account_value_ddpg)\n",
    "  perf_stats_all_ddpg = pd.DataFrame(perf_stats_all_ddpg)\n",
    "  perf_stats_all_ddpg.to_csv(\"./\"+config.RESULTS_DIR+\"/perf_stats_all_ddpg_\"+now+'.csv')\n",
    "\n",
    "if if_using_a2c:\n",
    "  print(\"\\n a2c:\")\n",
    "  perf_stats_all_a2c = backtest_stats(account_value=df_account_value_a2c)\n",
    "  perf_stats_all_a2c = pd.DataFrame(perf_stats_all_a2c)\n",
    "  perf_stats_all_a2c.to_csv(\"./\"+config.RESULTS_DIR+\"/perf_stats_all_a2c_\"+now+'.csv')\n",
    "\n",
    "if if_using_td3:\n",
    "  print(\"\\n atd3:\")\n",
    "  perf_stats_all_td3 = backtest_stats(account_value=df_account_value_td3)\n",
    "  perf_stats_all_td3 = pd.DataFrame(perf_stats_all_td3)\n",
    "  perf_stats_all_td3.to_csv(\"./\"+config.RESULTS_DIR+\"/perf_stats_all_td3_\"+now+'.csv')\n",
    "\n",
    "if if_using_sac:\n",
    "  print(\"\\n sac:\")\n",
    "  perf_stats_all_sac = backtest_stats(account_value=df_account_value_sac)\n",
    "  perf_stats_all_sac = pd.DataFrame(perf_stats_all_sac)\n",
    "  perf_stats_all_sac.to_csv(\"./\"+config.RESULTS_DIR+\"/perf_stats_all_sac_\"+now+'.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "QkV-LB66iwhD"
   },
   "outputs": [],
   "source": [
    "#baseline stats\n",
    "print(\"==============Get Baseline Stats===========\")\n",
    "baseline_df = get_baseline(\n",
    "        ticker=\"^DJI\", \n",
    "        start = TEST_START_DATE,\n",
    "        end = TEST_END_DATE)\n",
    "\n",
    "stats = backtest_stats(baseline_df, value_col_name = 'close')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9U6Suru3h1jc"
   },
   "source": [
    "<a id='6.2'></a>\n",
    "## 7.2 BackTestPlot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lKRGftSS7pNM"
   },
   "outputs": [],
   "source": [
    "print(\"==============Compare to DJIA===========\")\n",
    "%matplotlib inline\n",
    "# S&P 500: ^GSPC\n",
    "# Dow Jones Index: ^DJI\n",
    "# NASDAQ 100: ^NDX\n",
    "\n",
    "if if_using_ppo:\n",
    "  backtest_plot(df_account_value_ppo, \n",
    "              baseline_ticker = '^DJI', \n",
    "              baseline_start = TEST_START_DATE,\n",
    "              baseline_end = TEST_END_DATE)\n",
    "\n",
    "if if_using_ddpg:\n",
    "  backtest_plot(df_account_value_ddpg, \n",
    "              baseline_ticker = '^DJI', \n",
    "              baseline_start = TEST_START_DATE,\n",
    "              baseline_end = TEST_END_DATE)\n",
    "\n",
    "if if_using_a2c:\n",
    "  backtest_plot(df_account_value_a2c, \n",
    "              baseline_ticker = '^DJI', \n",
    "              baseline_start = TEST_START_DATE,\n",
    "              baseline_end = TEST_END_DATE)\n",
    "\n",
    "if if_using_td3:\n",
    "  backtest_plot(df_account_value_td3, \n",
    "              baseline_ticker = '^DJI', \n",
    "              baseline_start = TEST_START_DATE,\n",
    "              baseline_end = TEST_END_DATE)\n",
    "\n",
    "if if_using_sac:\n",
    "  backtest_plot(df_account_value_sac, \n",
    "              baseline_ticker = '^DJI', \n",
    "              baseline_start = TEST_START_DATE,\n",
    "              baseline_end = TEST_END_DATE)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "_gDkU-j-fCmZ",
    "MRiOtrywfAo1",
    "3Zpv4S0-fDBv",
    "Dr49PotrfG01"
   ],
   "name": "Stock_Fundamental.ipynb",
   "provenance": []
  },
  "gpuClass": "standard",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
